Una regla (RULE) para actualizar una tabla basada en la vista no hereda "automáticamente" las cláusulas DEFAULT de las columnas en la tabla. La regla de reescritura es muy explícita: hace lo que dice y nada más. El momento para manejar los valores por omisión (DEFAULT) es antes de la regla.

Dicho esto, las restricciones en la tabla son respetadas en la tabla base. Las columnas SERIAL también funcionan correctamente con reglas de UPDATE siempre y cuando el valor no se defina explícitamente.

Berend Tober tiene un caso de prueba particularmente útil para mostrar el comportamiento de reglas de UPDATE con cláusulas DEFAULT. Este caso de prueba muestra la creación de la tabla con valores DEFAULT para las columnas tres y cuatro, además de un SERIAL en la columna 2.

   CREATE TABLE test_table (
      field1 char(1) NOT NULL,
      field2 SERIAL,
      field3 INTEGER DEFAULT 1,
      field4 VARCHAR(24) DEFAULT '(default value)',
      CONSTRAINT testdb_pkey PRIMARY KEY (field2));
   
   CREATE VIEW test_table_v AS
      SELECT field1, field3, field4 FROM test_table;
   
   CREATE RULE test_table_rd AS ON DELETE TO test_table_v DO INSTEAD
      DELETE FROM test_table WHERE field1 = old.field1;
   
   CREATE RULE test_table_ri AS ON INSERT TO test_table_v DO INSTEAD
      INSERT INTO test_table (field1, field3, field4)
      VALUES (new.field1, new.field3, new.field4);
   
   CREATE RULE test_table_ru AS ON UPDATE TO test_table_v DO INSTEAD
      UPDATE test_table SET
         field1 = new.field1, field3 = new.field3, field4 = new.field4
      WHERE field1 = old.field1;
   
   INSERT INTO test_table VALUES ('A');
   INSERT INTO test_table_v VALUES ('B');
   
   -- Resultado --
   =# SELECT * FROM test_table;
    field1 | field2 | field3 |     field4      
   --------+--------+--------+-----------------
    A      |      1 |      1 | (default value)
    B      |      2 |        | 
   (2 rows)
   
   -- Por diversión, mostremos cómo funcionan las restricciones NOT NULL
   =# insert into test_table_v values (NULL);
   ERROR:  ExecInsert: Fail to add null value in not null attribute field1

La solución para tener vistas actualizables con valores DEFAULT apropiados es ALTER TABLE ... SET DEFAULT. La mayoría de los comandos ALTER TABLE no funcionarán en vistas, pero ALTER TABLE ... SET DEFAULT sí lo hará. También es posible que desee que la vista tenga valores por omisión distintos que los de la tabla, lo cual puede lograrse con ALTER TABLE.

   ALTER TABLE test_table_v
       ALTER field3 SET DEFAULT 1;
   ALTER TABLE test_table_v 
      ALTER field4 SET DEFAULT '(default value)';
                                                 
   =# insert into test_table_v VALUES ('C');
   INSERT 3702953 1
   # select * from test_table;
    field1 | field2 | field3 |     field4      
   --------+--------+--------+-----------------
    A      |      1 |      1 | (default value)
    B      |      2 |        | 
    C      |      3 |      1 | (default value)
   (3 rows)

Contribuyeron: btober at seaworthysys.com, Richard Huxton dev at archonet.com, Tom Lane tgl at sss.pgh.pa.us

Un objeto grande (large object) es un método de almacenamiento (no un tipo de dato) que permite que PostgreSQL almacene columnas grandes separadas de los datos del registro. El objeto grande aparece en el registro como un OID.

Una columna TOASTada es aquella donde el servidor automáticamente almacena valores grandes fuera de los registros, para varios tipos de dato. En la práctica, una columna TOASTada es invisible en la capa SQL.

Los objetos grandes ya no son tan necesarios como lo fueron antes de la introducción de TOAST en 7.1. Sin embargo, los objetos grandes pueden ser accedidos con las interfaces de cliente y servidor en C para hacer operaciones de archivos al estilo Unix como open, close, lseek, read y write. Los objetos TOASTados siempre son tratados como objetos completos.

El problema de los objetos grandes es que no se almacenan por separado automáticamente, y deben ser manejados por separado desde los valores en los registros. Esto significa que debe usar comandos especiales para insertar, actualizar y eliminar objetos grandes. pg_dump también tiene argumentos especiales para respaldar y recuperar los objetos grandes.

Si estuviera almacenando imágenes en la base de datos que luego quisiera extraer y desplegar, probablemente el tipo bytea serviría bien. La imagen, de ser grande, sería TOASTada. En cambio, si tuviera una aplicación de manejo de imágenes que manipulara o analizara el contenido de las imágenes, entonces quizás un almacenamiento en objetos grandes sería más apropiado. Con el almacenamiento en objetos grandes, podría hacer seek a una posición específica, leer y escribir los cambios usando la interfaz C. Si usara un valor TOASTado bytea, estaría obligado a leer la imagen completa, y a reemplazarla también completa, aunque quisiera cambiar un solo bit.

Las interfaces C también son útiles como funciones del lado del servidor para el análisis de los datos. Una función del lado del servidor en C, apropiada para el manejo de imágenes, puede posicionarse dentro de la imagen y buscar patrones, o efectuar transformaciones de la imagen, sin necesidad de transferir ninguna parte de ella hacia el cliente. pgtcl, la interfaz cliente, también soporta funcionalidad de objetos grandes.

Contribuyó: elein at varlena.com

Para crear una tabla que contenga un objeto grande, defina la tabla con una columna de tipo OID. El OID será el identificador del objeto grande en este caso.

El siguiente problema es cómo poner el objeto grande desde el sistema de archivos hacia la tabla. ¿Está el archivo del objeto en el lado del cliente, o en el lado del servidor? Un archivo en el lado del cliente es un archivo que vive en la máquina donde se ejecuta el cliente. Sólo es importante la distinción si la máquina del cliente es diferente del servidor de bases de datos. Un archivo en el lado del servidor es un archivo que reside en el servidor de bases de datos.

Hay una diferencia entre cargar el archivo en el lado del servidor y cargarlo en el lado del cliente. Para archivos en el lado del servidor, puede invocar lo_import() en la sentencia INSERT. Todo el trabajo es realizado por el servidor.

Para archivos en el cliente, el cliente tiene que arreglárselas para abrir el archivo y enviarlo al servidor. Esto se hace en el programa cliente. Puede ser hecho usando psql (que es un programa cliente especial) usando dos pasos. Primero se importa el objeto grande y luego se inserta el OID en la tabla usando la variable de psql :LASTOID.

Cuando selecciona un registro que contiene un objeto grande, lo que verá en la columna del objeto grande es el OID. Para acceder al contenido del objeto grande, debe extraerlo a un archivo o abrirlo y manipularlo en la interfaz cliente o la función del servidor.

Para extraer la imagen de la tabla misimagenes en el servidor, use la función lo_export(). Especifique la columna del objeto grande y el archivo de salida. El archivo de salida debe ser escribible por el servidor de bases de datos y será propiedad del usuario que ejecuta el proceso del servidor.

Para extraer la imagen a un archivo en el cliente, usando psql es un poco truculento. Sin usar un guión del intérprete de comandos (shell), debe ubicar el valor de la columna y luego usar la sentencia \lo_export (¡Si alguien sabe cómo hacer esto sólo con SQL y psql por favor hágame saber!).

El código que viene a continuación muestra la creación de una tabla que contiene un objeto grande. Luego carga la imagen desde el servidor y exporta una copia al servidor. Luego carga una imagen desde el cliente y exporta una copia hacia el cliente.

   --
   -- Tabla de imágenes
   --
   CREATE TABLE misimagenes (
      title TEXT NOT NULL primary key,
      picture OID);
   
   -- Imagen de rosas rojas está en el servidor.
   -- Cargar y exportar una copia al servidor.
   --
   INSERT INTO misimagenes (title, picture)
       VALUES ('Rosas Rojas', lo_import('/tmp/rosas_rojas.jpg'));
   SELECT lo_export(picture, '/tmp/rosas_rojas_copia.jpg') FROM misimagenes
       WHERE title = 'Rosas Rojas';
   
   --
   -- Imagen de rosas blancas está en el cliente
   -- Cargar y exportar una copia al cliente.
   --
   \lo_import '/tmp/rosas_blancas.jpg'
   INSERT INTO misimagenes (title, picture) VALUES ('Rosas Blancas', :LASTOID);
   
   SELECT * from misimagenes;
   
   --     title    | picture
   -- -------------+---------
   --  Red Roses   | 3715516
   --  White Roses | 3715518
   -- (2 rows)
   
   \lo_export 3715518 '/tmp/rosas_blancas_copia.jpg'    

Todo esto funciona. Pero, ¿Qué pasa cuando elimina un registro o actualiza un objeto grande? Eliminar el registro de la tabla en la manera usual elimina el registro, pero el objeto grande queda almacenado sin referencias. Puede mostrar que el objeto grande todavía existe usando \lo_list en psql. Este comando lista los OIDs de los objetos grandes que existen en el sistema.

Por otro lado, quizás el objeto grande no ha quedado sin referencias porque otro registro hace referencia a la misma imagen. Puesto que los objetos grandes son, hum, grandes, se pensó que era mejor pasar el identificador del objeto, el OID. Ese OID puede ser referido por otros registros si el desarrollador no desea mantener múltiples copias del mismo objeto, y ese OID también puede estar involucrado en una transacción en el mismo registro o en otros. Este es un problema que el desarrollador debe resolver.

En este ejemplo, asumiremos que cuando un registro es eliminado entonces también deseamos eliminar el objeto grande. Esto implica que nunca habrá otro registro que haga referencia al mismo objeto grande.

Para hacer esto, use una regla para llamar a la función lo_unlink(). Usando la interfaz de objetos grandes es posible escribir objetos grandes y modificarlos. Cuando está usando sólo SQL, entonces debe eliminar y reemplazar el objeto grande. Esto también puede hacerse con una regla: cuando asigne nuevos valores a los objetos grandes, simplemente elimine el antiguo. Pero sólo elimine si está recibiendo un valor nuevo.

   CREATE RULE droppicture AS ON DELETE TO misimagenes 
   DO SELECT lo_unlink( OLD.picture );

   CREATE RULE reppicture AS ON UPDATE TO misimagenes 
   DO SELECT lo_unlink( OLD.picture ) where OLD.picture <> NEW.picture;

   =# select * from misimagenes;
       title      | picture 
   ---------------+---------
    Rosas Blancas | 3715592
    Rosas Rojas   | 3715593
   (2 rows)

   =# update misimagenes set picture=lo_import('/tmp/rosas_rojas_copia.jpg') 
      where title='Rosas Rojas';
    lo_unlink 
   -----------
            1
   (1 row)
   
   =# \lo_list
        Large objects
      ID    | Description 
   ---------+-------------
    3715592 | 
    3715598 | 
   (2 rows)
   
   =# select * from misimagenes;
       title      | picture 
   ---------------+---------
    Rosas Blancas | 3715592
    Rosas Rojas   | 3715598
   (2 rows)

Precación: note que si elimina un objeto grande que está siendo referido por un registro existente Y tiene estas reglas funcionando, no puede eliminar el registro. Debe eliminar la regla, borrar el registro y volver a crear la regla. LO_TRANSACTION, la variable que afecta las transacciones para operaciones con objetos grandes, no tiene efecto sobre esta funcionalidad, y no existe en 7.4.

Contribuyó: elein at varlena.com

La intención original de los diseñadores de Postgres era que un objeto grande (blob) fuera parte de un tipo de dato definido por el usuario. La diferencia es la que hay entre un blob inteligente y un blob tonto. El blob inteligente "conoce" su tipo de dato y las operaciones y funciones que le son propias.

Por ejemplo, se podría crear un tipo de documento, y podría contener el objeto grande, junto con algunos atributos interesantes del documento como un conjunto de palabras clave, la cantidad de palabras, el tamaño y el idioma del documento.

Se podrían escribir funciones para manejar el documento. Las funciones básicas podrían incluir:

• Entrada desde funciones de texto (requerida). Esto podría calcular el tamaño y la cantidad de palabras durante la inserción. Y manejaría el objeto grande usando las rutinas para objetos grandes.
• Salida a funciones de texto (requerida). La salida a texto no necesariamente tendría que emitir el documento mismo.
• Una comparación de igualdad. Serían posibles varios tipos de comparaciones:
  • Comparar el texto con o sin espacios
  • Comparar el ID del objeto grande
  • Comparar todos o algunos de los atributos del documento.
• Menor que y mayor que
• Quizás una comparación de la cantidad de palabras o el tamaño total del documento.
• Funciones "get" y "set" para todos los atributos, incluyendo el documento mismo.

• Revisión ortográfica, con o sin corrección.
• Un exportador para imprimir la salida a un archivo o impresora.
• Un sistema de búsqueda.
• Búsqueda por palabras clave.
• Búsqueda en todo el texto.
• Indexación inteligente para extraer palabras clave desde el texto.

La implementación de este tipo de documentos se deja como ejercicio para el lector :-) Cualquier similitud entre este tipo de documentos y algún tipo existente para almacenar documentos es solamente sentido común y coincidencia.

Todas estas ideas tienen que ver, más que nada, con la creación de cualquier tipo de dato y pretenden ejemplificar por qué los objetos grandes no necesariamente tienen que ser blobs tontos.

Contribuyó: elein at varlena.com