PostgreSQL-Objetos Binarios Grandes

Introducción

En PostgreSQL existen varias formas de administrar Objetos Binarios Grandes (LOB, BLOB):

  1. Tipo de datos binario básico BYTEA
  2. Tipo de datos de carácter básico TEXT
  3. Facilidad de objeto grande (LO) ‘pg_largeobject’
  4. Tipo de datos ENLACE de datos (que es solo espec., sin aplicación)

PostgreSQL también admite un sistema de almacenamiento llamado «TOAST» (La Técnica de Almacenamiento de Atributos Sobredimensionados) que almacena automáticamente valores mayores que una sola página de base de datos (normalmente 8 KB) en un área de almacenamiento secundario por tabla. Esto define el límite de tamaño de cualquier columna / campo a 1 GB.

Aquí hay algunas preguntas de diseño e implementación sobre objetos grandes:

  • ¿El LO (conceptualmente) es parte del objeto/entidad, o simplemente está asociado con él?
  • ¿Cómo se accede a la LO: como cadena codificada, como archivo o basada en flujo?
  • Si no se almacena dentro de la tabla, ¿quién mantiene la integridad del LO?

Importar/Exportar Archivos Binarios externos

Cuando se usa BYTEA y TEXTO (y XML), la forma común de importar/exportar archivos externos es dentro de los programas cliente (por ejemplo, Java/Python) o servidor (por ejemplo, PL/Python). Es difícil manejar archivos externos con psql.

Véase Stackexchange: .

BYTEA

El tipo de datos BYTEA permite el almacenamiento de cadenas binarias.

  • Almacena un LOB dentro de la tabla, respectivamente, usando TOAST.
  • Por lo tanto, está limitado a 1 GB
  • El almacenamiento es octal y permite caracteres no imprimibles (a diferencia de las cadenas de caracteres que no lo hacen).
  • El formato de entrada / salida es hexadecimal (a partir de PostgreSQL 9.0).

Notas:

  • BYTEA se acerca al tipo de cadena binaria estándar SQL ‘BLOB’. Las funciones y operadores proporcionados por BYTEA son en su mayoría los mismos, mientras que el formato de entrada hexadecimal de BYTEA es diferente.
  • BYTEA es más lento para longitudes > 20 MB que la instalación LO (no tiene accesos aleatorios).

Consulte Tipos de datos Binarios doc de PostgreSQL.

TEXTO

Tipo de datos básicos texto está aquí para completar. Este es un tipo de carácter variable con longitud ilimitada (hasta 1 GB). los tipos de caracteres permiten la configuración regional.

No es una cadena de bytes, pero todavía se puede usar cuando la cadena binaria está preprocesada y codificada en forma imprimible (por ejemplo, base64 o hex).

Instalación de objetos grandes (LO)

Los objetos grandes (LO) también se pueden colocar en una única tabla del sistema llamada ‘pg_largeobject’ a la que se debe acceder a través de identificadores de tipo de datos OID.

  • Hay una API de lectura/escritura que ofrece funciones cliente (= módulos escritos en C) y del lado del servidor (= SQL).
  • Las principales funciones SQL relacionadas son: lo_creat(), lo_create(), lo_unlink(), lo_import () y lo_export(). lo_import() y lo_export () necesitan permisos del usuario propietario de la base de datos (es decir, superusuario).
  • LO se dividen en» trozos » y se almacenan en filas indexadas por árboles.
  • LO permite valores de hasta 2 GB de tamaño, mientras que los campos tostados (como BYTEA) pueden tener como máximo 1 GB.
  • Las entradas de LO se pueden modificar aleatoriamente utilizando una API de lectura / escritura que es más eficiente que realizar dichas operaciones con TOAST (y, por ejemplo, BYTEA).

Nota:

  • Cuando PostgreSQL doc. menciona ‘ lo ‘ (LO = Objeto grande) se refiere típicamente a esta instalación.
  • En contraste con, por ejemplo, BYTEA-LO no es un tipo de datos en sí mismo, sino una tabla, una «instalación».

NOTA IMPORTANTE cuando se usa BLOB JDBC (o anotación @Lob en Hibernación):

  • Dado que PostgreSQL considera una entrada LO como un objeto por sí mismo, eliminar o subir filas en la tabla de usuarios no elimina ni elimina entradas en pg_largeobjects. por lo tanto, pg_largeobjects crece infinitamente a menos que se realice una limpieza separada (consulte este informe de error en el foro de Hibernación).
  • Para evitar esto, normalmente se necesita agregar un disparador que elimine las entradas en pg_largeobject como descriebd en el módulo ‘lo’.
  • Vea módulos adicionales de PostgreSQL ‘ lo ‘y’ vaccumlo ‘ en los documentos de PostgreSQL.

Consulte el documento de PostgreSQL ‘Objetos grandes’ y el BLOB de tipo de datos JDBC: .

Ejemplo: Uso típico en SQL (basado en documentos de 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';

ENLACE DE DATOS

NOTA: Actualmente no hay implementación en PostgreSQL para eso. Es solo una especificación definida en SQL estándar ‘SQL/MED’.

El tipo de enlace de datos almacena las direcciones URL de los archivos en columnas de la base de datos y le aplica restricciones.

  • Mantiene un enlace a un archivo específico en almacenamiento externo.
  • El sistema de base de datos toma el control de los archivos externos (renombrar, eliminar, los permisos se hacen con SQL) si así se define.
  • El tamaño del archivo es ilimitado, limitado respectivamente por el almacenamiento externo. No es necesario almacenar el contenido de los archivos en el sistema de base de datos.

Parámetros de enlace de datos:

  • El valor de enlace de datos no necesita hacer referencia a un archivo/URL existente.
  • El valor de enlace de datos de CONTROL DE enlace DE archivo debe hacer referencia a un archivo/URL existente.
  • INTEGRIDAD Todos los archivos a los que se hace referencia solo se pueden cambiar de nombre o eliminar a través de SQL.
  • Los archivos de referencia selectivos de INTEGRIDAD se pueden cambiar de nombre o eliminar a través de SQL o directamente.
  • INTEGRIDAD NINGUNO (implícito para NINGÚN CONTROL DE ENLACE)
  • AL DESVINCULAR, el archivo de ELIMINACIÓN se elimina del sistema de archivos cuando se elimina de la base de datos.
  • AL DESVINCULAR, los permisos originales del archivo DE restauración se restauran cuando se eliminan de la base de datos.
  • AL DESVINCULAR NINGUNO No hay cambios en los permisos de archivo cuando se elimina la referencia de archivo de la base de datos.
  • RECUPERACIÓN SÍ PITR se aplica a los archivos de referencia.
  • RECOVERY NO PITR no se aplica a los archivos de referencia.

Estado e instalación:

  • no está claro.

Ejemplo:

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

Enlaces web:

  • Página wiki de enlace de DATOS
  • Presentación de Peter Eisentraut, PGCon 2009:
  • Tesis de seminario MSE (2011) de Florian Schwendener sobre «SQL / MED y Más – Gestión de Datos externos en PostgreSQL y Microsoft SQL Server»:

Deja una respuesta

Tu dirección de correo electrónico no será publicada.