PostgreSQL-binaire grote objecten

Inleiding

in PostgreSQL bestaan verschillende manieren om binaire grote objecten te beheren (LOB, BLOB):

  1. Basic binary data type BYTEA
  2. basic character data type TEXT
  3. Large object (LO) faciliteit ‘pg_largeobject’
  4. gegevenstype DATALINK (dat is slechts spec., geen implementatie)

PostgreSQL ondersteunt ook een opslagsysteem genaamd “TOAST” (de oversized-Attribute Storage Technique) dat automatisch waarden groter dan een enkele databasepagina (meestal 8 KB) opslaat in een secundair opslaggebied per tabel. Dit definieert de limiet van de grootte van een kolom / veld tot 1 GB.

hier zijn enkele ontwerp-en implementatievragen met betrekking tot grote objecten:

  • is de LO (conceptueel) deel van het object/entiteit – of gewoon geassocieerd met het?
  • hoe wordt de LO benaderd: als gecodeerde tekenreeks, als bestand of stream-based?
  • indien niet opgeslagen in de tabel, Wie behoudt de integriteit van de LO?

importeren / exporteren van externe binaire bestanden

bij gebruik van BYTEA en tekst (en XML) is de gebruikelijke manier om externe bestanden te importeren/exporteren binnen de client (bijvoorbeeld Java/Python) of serverside programma ‘ s (bijvoorbeeld PL/Python). Het is moeilijk om externe bestanden te verwerken met psql.

Zie Stackexchange: .

BYTEA

het BYTEA-gegevenstype maakt opslag van binaire tekenreeksen mogelijk.

  • het slaat een LOB op in de tabel, respectievelijk met behulp van TOAST.
  • het is dus beperkt tot 1 GB
  • de opslag is octaal en staat niet-afdrukbare tekens toe (in tegenstelling tot tekenreeksen die dat niet doen).
  • het invoer / uitvoerformaat is HEX (vanaf PostgreSQL 9.0).

opmerkingen:

  • BYTEA komt dicht bij de SQL standaard binaire string type ‘BLOB’. De functies en operators die door BYTEA worden verstrekt zijn meestal hetzelfde, terwijl het HEXUITDRAAIINVOERFORMAAT van BYTEA verschillend is.
  • BYTEA is langzamer voor lengtes > 20 MB dan de lo-faciliteit (het heeft geen willekeurige toegang).

zie binaire gegevenstypen van PostgreSQL doc.

TEXT

Basic data type text Het is gewoon hier voor de volledigheid. Dit is een variabel karaktertype met onbeperkte lengte (tot 1 GB). tekentypen staan lokale instellingen toe.

het is geen byte string, maar men zou het nog steeds kunnen gebruiken wanneer de binaire string vooraf is verwerkt en gecodeerd in afdrukbare vorm (bijvoorbeeld base64 of hex).

faciliteit voor grote objecten (LO)

grote objecten (LO) kunnen ook worden geplaatst in een enkele systeemtabel met de naam ‘pg_largeobject’ die moet worden benaderd via identifiers van het gegevenstype OID.

  • er is een lees/schrijf-API die client (= modules geschreven in C) en server-side (= SQL) functies biedt.
  • de belangrijkste gerelateerde SQL-functies zijn: lo_creat(), lo_create(), lo_unlink(), lo_import (), en lo_export (). lo_import () en lo_export() hebben permissies nodig van de eigenaar van de database gebruiker (dat wil zeggen superuser).
  • LO worden opgesplitst in” brokken ” en opgeslagen in rijen met drie indexen.
  • LO laat waarden toe tot 2 GB groot, terwijl geroosterde velden (zoals BYTEA) maximaal 1 GB kunnen zijn.
  • LO items kunnen willekeurig worden gewijzigd met behulp van een lees/schrijf-API die efficiënter is dan het uitvoeren van dergelijke bewerkingen met behulp van TOAST (en bijvoorbeeld BYTEA).

Noot:

  • wanneer PostgreSQL doc. noemt ‘ lo ‘ (LO = groot Object) het verwijst meestal naar deze faciliteit.
  • in tegenstelling tot bijvoorbeeld BYTEA – LO is geen gegevenstype op zichzelf, maar een tabel, een “faciliteit”.

belangrijke opmerking bij gebruik van JDBC BLOB (of @Lob annotatie in de slaapstand):

  • omdat PostgreSQL een lo-item als een object op zichzelf beschouwt, worden in pg_largeobjects geen items verwijderd of verwijderd door rijen in de gebruikerstabel te verwijderen of te uppen. pg_largeobjects groeit daarom oneindig tenzij een aparte reiniging wordt gemaakt (zie dit foutrapport in Hibernate forum).
  • om dit te voorkomen, moet doorgaans een trigger worden toegevoegd die items in pg_largeobject verwijdert als descriptiebd in module ‘lo’.
  • zie aanvullende PostgreSQL-modules ” lo ” en “vaccumlo” in de PostgreSQL-docs.

zie PostgreSQL doc ‘grote objecten’ en JDBC gegevenstype BLOB: .

voorbeeld: typisch gebruik in SQL (gebaseerd op Postgres docs):

 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

Opmerking: Er is momenteel geen implementatie in PostgreSQL hiervoor. Het is gewoon een specificatie gedefinieerd in standaard SQL ‘SQL / MED’.

het type DATALINK slaat bestands-URL ‘ s op in Databasekolommen en past er beperkingen op toe.

  • onderhoudt een koppeling naar een specifiek bestand in externe opslag.
  • databasesysteem neemt de controle over externe bestanden over (hernoemen, verwijderen, machtigingen worden gedaan met SQL) indien dit is gedefinieerd.
  • Bestandsgrootte is onbeperkt, respectievelijk beperkt door externe opslag. Het is niet nodig om de inhoud van het bestand op te slaan in het databasesysteem.

datalinkparameters:

  • geen LINK controle datalink waarde hoeft niet te verwijzen naar een bestaand bestand / URL.
  • FILE LINK CONTROL datalink waarde moet verwijzen naar een bestaand bestand / URL.
  • integriteit alle bestanden waarnaar verwezen wordt kunnen alleen hernoemd of verwijderd worden via SQL.
  • bestanden met selectieve verwijzingen naar integriteit kunnen hernoemd of verwijderd worden via SQL of direct.
  • integriteit geen (impliciet voor geen LINKCONTROLE)
  • bij verwijderen van koppeling wordt verwijderd uit het bestandssysteem wanneer verwijderd uit de database.
  • bij het ongedaan maken van de koppeling worden de oorspronkelijke rechten hersteld wanneer ze uit de database worden verwijderd.
  • on UNLINK NONE Geen wijziging in bestandsmachtigingen wanneer bestandsreferentie uit de database wordt verwijderd.
  • herstel Ja PITR is van toepassing op bestanden waarnaar wordt verwezen.
  • RECOVERY geen PITR is niet van toepassing op bestanden waarnaar wordt verwezen.

Status en installatie:

  • onduidelijk.

voorbeeld:

 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
  • presentatie van Peter Eisentraut, PGCon 2009:
  • MSE-Seminar Thesis (2011) van Florian Schwendener over “SQL / MED and More – Management of External Data in PostgreSQL and Microsoft SQL Server”:

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.