PostgreSQL – Objets Binaires Volumineux

Introduction

Dans PostgreSQL, il existe plusieurs façons de gérer des Objets Binaires Volumineux (LOB, BLOB):

  1. Type de données binaires de base OCTEA
  2. Caractère de base type de données TEXTE
  3. Objet volumineux (LO) installation ‘pg_largeobject’
  4. Type de données LIEN de DONNÉES (c’est juste spec., aucune mise en œuvre)

PostgreSQL prend également en charge un système de stockage appelé « TOAST » (La technique de stockage à attributs surdimensionnés) qui stocke automatiquement des valeurs supérieures à une seule page de base de données (généralement 8 Ko) dans une zone de stockage secondaire par table. Cela définit la limite de taille de n’importe quelle colonne / champ à 1 Go.

Voici quelques questions de conception et de mise en œuvre concernant les objets de grande taille:

  • Le LO fait-il (conceptuellement) partie de l’objet / de l’entité – ou simplement y est-il associé?
  • Comment le LO est-il accessible: en tant que chaîne codée, en tant que fichier ou en fonction du flux?
  • S’il n’est pas stocké dans la table, qui maintient l’intégrité de la LO ?

Importation / Exportation de fichiers Binaires externes

Lorsque vous utilisez des OCTETS et du TEXTE (et XML), la manière courante d’importer / exporter des fichiers externes est à l’intérieur du client (par exemple Java / Python) ou des programmes côté serveur (par exemple PL / Python). Il est difficile de gérer des fichiers externes avec psql.

Voir l’échange de piles : .

BYTEA

Le type de données BYTEA permet le stockage de chaînes binaires.

  • Il stocke un LOB dans la table, en utilisant respectivement TOAST.
  • Il est ainsi limité à 1 Go
  • Le stockage est octal et permet des caractères non imprimables (contrairement aux chaînes de caractères qui ne le sont pas).
  • Le format d’entrée/sortie est HEX (à partir de PostgreSQL 9.0).

Notes:

  • BYTEA se rapproche du type de chaîne binaire standard SQL ‘BLOB’. Les fonctions et les opérateurs fournis par BYTEA sont pour la plupart les mêmes, tandis que le format d’entrée hexadécimal de BYTEA est différent.
  • BYTEA est plus lent pour des longueurs > 20 Mo que l’installation LO (elle n’a pas d’accès aléatoires).

Voir Types de données binaires doc PostgreSQL.

TEXTE

Type de données de base texte c’est juste ici pour être complet. Il s’agit d’un type de caractère variable avec une longueur illimitée (jusqu’à 1 Go). les types de caractères autorisent les paramètres régionaux.

Ce n’est pas une chaîne d’octets mais on peut toujours l’utiliser lorsque la chaîne binaire est prétraitée et codée sous forme imprimable (par exemple base64 ou hex).

Objet volumineux (LO)

Les objets volumineux (LO) peuvent également être placés dans une seule table système appelée ‘pg_largeobject’ à laquelle il faut accéder via des identifiants de type de données O.

  • Il existe une API de lecture/écriture qui offre des fonctions client (= modules écrits en C) et côté serveur (= SQL).
  • Les principales fonctions SQL associées sont : lo_creat(), lo_create(), lo_unlink(), lo_import() et lo_export(). lo_import() et lo_export() ont besoin des autorisations de l’utilisateur propriétaire de la base de données (c’est-à-dire du superutilisateur).
  • LO sont divisés en « morceaux » et stockés dans des lignes indexées btree.
  • LO permet des valeurs allant jusqu’à 2 Go, tandis que les champs grillés (comme BYTEA) peuvent atteindre au plus 1 Go.
  • Les entrées LO peuvent être modifiées de manière aléatoire à l’aide d’une API de lecture/ écriture plus efficace que d’effectuer de telles opérations à l’aide de TOAST (et par exemple BYTEA).

Remarque:

  • Lorsque PostgreSQL doc. mentionne ‘lo’ (LO = Objet volumineux) il fait généralement référence à cette installation.
  • Contrairement à par exemple BYTEA-LO n’est pas un type de données en soi mais une table, une « installation ».

REMARQUE IMPORTANTE lors de l’utilisation du BLOB JDBC (ou de l’annotation @Lob dans Hibernate):

  • Étant donné que PostgreSQL considère une entrée LO comme un objet à part entière, la suppression ou l’ajout de lignes dans la table utilisateur ne supprime pas ou ne supprime pas les entrées dans pg_largeobjects. pg_largeobjects se développe donc à l’infini à moins qu’un nettoyage séparé ne soit effectué (voir ce rapport d’erreur dans le forum Hibernate).
  • Pour éviter cela, il faut généralement ajouter un déclencheur qui supprime les entrées de pg_largeobject en tant que descriebd dans le module ‘lo’.
  • Voir les modules PostgreSQL supplémentaires ‘lo’ et ‘vaccumlo’ dans les documents PostgreSQL.

Voir le document PostgreSQL ‘Objets volumineux’ et le type de données BLOB JDBC: .

Exemple: Utilisation typique en SQL (basée sur les documents Postgres):

 CREATE TABLE image ( id integer, name text, picture oid ); SELECT lo_creat(-1); -- returns OID of new, empty large object. SELECT lo_create(43213); -- attempts to create large object with OID 43213. SELECT lo_unlink(173454); -- deletes large object with OID 173454. INSERT INTO image (id, name, picture) VALUES (1, 'beautiful image', lo_import('/etc/motd')); INSERT INTO image (id, name, picture) -- same as above, but specify OID to use. VALUES (1, 'beautiful image', lo_import('/etc/motd', 68583)); SELECT lo_export(image.raster, '/tmp/motd') FROM image -- need superuser permission. WHERE name = 'beautiful image';

DATALINK

REMARQUE: Il n’y a actuellement aucune implémentation dans PostgreSQL pour cela. C’est juste une spécification définie dans SQL standard ‘SQL / MED’.

Le type DATALINK stocke les URL de fichier dans les colonnes de la base de données et y applique des contraintes.

  • Maintient un lien vers un fichier spécifique dans un stockage externe.
  • Le système de base de données prend le contrôle des fichiers externes (renommer, supprimer, les autorisations sont effectuées avec SQL) si défini ainsi.
  • La taille du fichier est illimitée, respectivement limitée par le stockage externe. Pas besoin de stocker le contenu du fichier dans le système de base de données.

Paramètres de LIAISON de DONNÉES:

  • AUCUNE valeur DE LIAISON DE données DE CONTRÔLE DE LIEN n’a besoin de référencer un fichier/URL existant.
  • CONTRÔLE DE LIEN DE FICHIER La valeur de lien de données doit référencer un fichier/URL existant.
  • INTÉGRITÉ TOUS les fichiers référencés ne peuvent être renommés ou supprimés que via SQL.
  • Les fichiers référencés SÉLECTIFS d’INTÉGRITÉ peuvent être renommés ou supprimés via SQL ou directement.
  • INTÉGRITÉ AUCUN (implicite pour AUCUN CONTRÔLE DE LIEN)
  • SUR UNLINK DELETE Le fichier est supprimé du système de fichiers lorsqu’il est supprimé de la base de données.
  • SUR UNLINK RESTORE, les autorisations d’origine du fichier sont restaurées lorsqu’elles sont supprimées de la base de données.
  • SUR DISSOCIER AUCUN Changement dans les autorisations de fichier lorsque la référence de fichier est supprimée de la base de données.
  • RÉCUPÉRATION OUI PITR s’applique aux fichiers référencés.
  • RECOVERY NO PITR ne s’applique pas aux fichiers référencés.

Statut et installation:

  • pas clair.

Exemple:

 CREATE TABLE image ( id integer, name text, picture DATALINK ); INSERT INTO persons VALUES ( 1, 'Jon Doe', DLVALUE('file://some/where/1.jpg') );

Liens Web:

  • Page wiki DATALINK
  • Présentation de Peter Eisentraut, PGCon 2009 :
  • Thèse de séminaire MSE (2011) de Florian Schwendener sur « SQL/MED et Plus – Gestion des Données Externes dans PostgreSQL et Microsoft SQL Server » :

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.