PostgreSQL – Binary Large Objects

Einführung

In PostgreSQL gibt es mehrere Möglichkeiten, binäre große Objekte (LOB, BLOB) zu verwalten):

  1. Grundlegender binärer Datentyp BYTEA
  2. Grundlegender Zeichendatentyp TEXT
  3. Large object (LO) facility ‚pg_largeobject‘
  4. Datentyp DATALINK (das ist nur spec., keine Implementierung)

PostgreSQL unterstützt auch ein Speichersystem namens „TOAST“ (Die Speichertechnik für übergroße Attribute), das automatisch Werte, die größer als eine einzelne Datenbankseite (normalerweise 8 KB) sind, in einem sekundären Speicherbereich pro Tabelle speichert. Dies definiert die Größenbeschränkung einer Spalte / eines Felds auf 1 GB.

Hier sind einige Design- und Implementierungsfragen zu großen Objekten:

  • Ist das LO (konzeptionell) Teil des Objekts / der Entität – oder nur damit verbunden?
  • Wie wird auf das LO zugegriffen: als codierte Zeichenfolge, als Datei oder Stream-basiert?
  • Wer behält die Integrität der LO bei, wenn sie nicht in der Tabelle gespeichert ist?

Import / Export externer Binärdateien

Bei Verwendung von BYTEA und TEXT (und XML) erfolgt der Import / Export externer Dateien üblicherweise in Client- (z. B. Java / Python) oder serverseitigen Programmen (z. B. PL / Python). Es ist schwierig, externe Dateien mit psql zu verarbeiten.

Siehe Stackexchange: .

BYTEA

Der Datentyp BYTEA erlaubt die Speicherung von binären Strings.

  • Es speichert ein LOB in der Tabelle, jeweils mit TOAST.
  • Es ist somit auf 1 GB begrenzt
  • Der Speicher ist oktal und erlaubt nicht druckbare Zeichen (im Gegensatz zu Zeichenketten, die dies nicht tun).
  • Das Eingabe-/Ausgabeformat ist HEX (ab PostgreSQL 9.0).

Anmerkungen:

  • BYTEA kommt dem SQL-Standard-Binärzeichenfolgentyp ‚BLOB‘ nahe. Die von BYTEA bereitgestellten Funktionen und Operatoren sind größtenteils gleich, während das HEX-Eingabeformat von BYTEA unterschiedlich ist.
  • BYTEA ist für Längen > 20 MB langsamer als die LO-Funktion (es gibt keinen zufälligen Zugriff).

Siehe PostgreSQL doc Binäre Datentypen.

TEXT

Basisdatentyp Text es ist nur der Vollständigkeit halber hier. Dies ist ein variabler Zeichentyp mit unbegrenzter Länge (bis zu 1 GB). zeichentypen erlauben Gebietsschemaeinstellungen.

Es ist keine Byte-Zeichenfolge, aber man könnte sie immer noch verwenden, wenn die binäre Zeichenfolge vorverarbeitet und in druckbare Form codiert wird (z. B. base64 oder Hex).

Large object (LO) und

Large objects (LO) können auch in einer einzigen Systemtabelle namens ‚pg_largeobject‘ abgelegt werden, auf die über Bezeichner des Datentyps OID zugegriffen werden muss.

  • Es gibt eine Lese- / Schreib-API, die Client- (= in C geschriebene Module) und serverseitige (= SQL) Funktionen bietet.
  • Die wichtigsten verwandten SQL-Funktionen sind: lo_creat(), lo_create(), lo_unlink(), lo_import() und lo_export(). lo_import() und lo_export() benötigen Berechtigungen des Datenbankbesitzers (dh Superuser).
  • LO werden in „Chunks“ aufgeteilt und in btree-indizierten Zeilen gespeichert.
  • LO erlaubt Werte bis zu 2 GB, wohingegen getoastete Felder (wie BYTEA) höchstens 1 GB groß sein können.
  • LO-Einträge können zufällig mit einer Lese- / Schreib-API geändert werden, die effizienter ist als die Durchführung solcher Operationen mit TOAST (und z. B. BYTEA).

Hinweis:

  • Wenn PostgreSQL doc. erwähnt ‚lo‘ (LO = Großes Objekt) es bezieht sich typischerweise auf diese Einrichtung.
  • Im Gegensatz zu z.B. BYTEA – LO ist kein Datentyp für sich, sondern eine Tabelle, eine ‚Facility‘.

WICHTIGER HINWEIS bei Verwendung von JDBC BLOB (oder @Lob Annotation im Ruhezustand):

  • Da PostgreSQL einen Benutzereintrag als eigenständiges Objekt betrachtet, löscht oder löscht das Löschen oder Deaktivieren von Zeilen in der Benutzertabelle keine Einträge in pg_largeobjects . pg_largeobjects wächst daher unendlich, es sei denn, es wird eine separate Bereinigung durchgeführt (siehe diesen Fehlerbericht im Hibernate-Forum ).
  • Um dies zu verhindern, muss in der Regel ein Trigger hinzugefügt werden, der Einträge in pg_largeobject löscht, wie im Modul ‚lo‘ beschrieben.
  • Siehe zusätzliche PostgreSQL-Module ‚lo‘ und ‚vaccumlo‘ in den PostgreSQL-Dokumenten.

Siehe PostgreSQL-Dokument ‚Große Objekte‘ und JDBC-Datentyp BLOB: .

Beispiel: Typische Verwendung in SQL (basierend auf Postgres-Dokumenten):

 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

HINWEIS: Dafür gibt es derzeit keine Implementierung in PostgreSQL. Es ist nur eine Spezifikation, die in Standard-SQL ‚SQL / MED‘ definiert ist.

Der DATALINKTYP speichert Datei-URLs in Datenbankspalten und wendet Einschränkungen darauf an.

  • Verwaltet einen Link zu einer bestimmten Datei im externen Speicher.
  • Das Datenbanksystem übernimmt die Kontrolle über externe Dateien (Umbenennen, Löschen, Berechtigungen erfolgen mit SQL), wenn dies definiert ist.
  • Die Dateigröße ist unbegrenzt bzw. durch externen Speicher begrenzt. Keine Notwendigkeit, Dateiinhalte im Datenbanksystem zu speichern.

DATALINK-Parameter:

  • KEIN LINK CONTROL Datalink-Wert muss nicht auf eine vorhandene Datei / URL verweisen.
  • Der Datalink-Wert des DATEIVERKNÜPFUNGSSTEUERELEMENTS muss auf eine vorhandene Datei/URL verweisen.
  • INTEGRITÄT ALLE referenzierten Dateien können nur über SQL umbenannt oder gelöscht werden.
  • INTEGRITÄT SELEKTIV referenzierte Dateien können über SQL oder direkt umbenannt oder gelöscht werden.
  • INTEGRITY NONE (implizit für NO LINK CONTROL)
  • BEIM AUFHEBEN der VERKNÜPFUNG wird die Löschdatei aus dem Dateisystem gelöscht, wenn sie aus der Datenbank gelöscht wird.
  • BEIM AUFHEBEN der VERKNÜPFUNG werden die ursprünglichen Berechtigungen der Wiederherstellungsdatei wiederhergestellt, wenn sie aus der Datenbank gelöscht werden.
  • ON VERKNÜPFUNG AUFHEBEN KEINE Keine Änderung der Dateiberechtigungen, wenn der Dateiverweis aus der Datenbank gelöscht wird.
  • WIEDERHERSTELLUNG JA PITR gilt für referenzierte Dateien.
  • RECOVERY NO PITR gilt nicht für referenzierte Dateien.

Status und Installation:

  • unklar.

Beispiel:

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

Weblinks:

  • DATALINK wiki page
  • Präsentation von Peter Eisentraut, PGCon 2009:
  • MSE-Seminararbeit (2011) von Florian Schwendener zum Thema „SQL/MED and More – Management externer Daten in PostgreSQL und Microsoft SQL Server“:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.