Las reglas ON SELECT se aplican a todas las consultas como último paso, incluso si el comando debido es un INSERT, UPDATE o DELETE. Y tienen una semántica diferente de las reglas en los otros tipos de comandos, ya que modifican el árbol de consulta directamente en lugar de crear uno nuevo. Por lo tanto, las reglas SELECT se describen primero.

Actualmente, solo puede haber una acción en una regla ON SELECT, y debe ser una acción SELECT incondicional que sea INSTEAD. Esta restricción era necesaria para que las reglas fueran lo suficientemente seguras como para abrirlas a los usuarios comunes, y restringe las reglas ON SELECT a actuar como vistas.

Los ejemplos de este capítulo son dos vistas de unión (joins) que realizan algunos cálculos y algunas vistas más que las utilizan a su vez. Una de las dos primeras vistas se personaliza más adelante agregando reglas para las operaciones INSERT, UPDATE y DELETE, de modo que el resultado final será una vista que se comporta como una tabla real con cierta funcionalidad mágica. Este no es un ejemplo tan simple para empezar y hace que sea más difícil entrar en el tema. Pero es mejor tener un único ejemplo que cubra todos los puntos discutidos paso a paso en lugar de tener muchos diferentes que podrían confundirse en la mente.

Las tablas reales que necesitamos en las descripciones de los dos primeros sistemas de reglas son estas:

CREATE TABLE shoe_data (
    shoename   text,          -- primary key
    sh_avail   integer,       -- available number of pairs
    slcolor    text,          -- preferred shoelace color
    slminlen   real,          -- minimum shoelace length
    slmaxlen   real,          -- maximum shoelace length
    slunit     text           -- length unit
);

CREATE TABLE shoelace_data (
    sl_name    text,          -- primary key
    sl_avail   integer,       -- available number of pairs
    sl_color   text,          -- shoelace color
    sl_len     real,          -- shoelace length
    sl_unit    text           -- length unit
);

CREATE TABLE unit (
    un_name    text,          -- primary key
    un_fact    real           -- factor to transform to cm
);

Como puedes ver, representan datos de una zapatería.

Las vistas se crean como:

CREATE VIEW shoe AS
    SELECT sh.shoename,
           sh.sh_avail,
           sh.slcolor,
           sh.slminlen,
           sh.slminlen * un.un_fact AS slminlen_cm,
           sh.slmaxlen,
           sh.slmaxlen * un.un_fact AS slmaxlen_cm,
           sh.slunit
      FROM shoe_data sh, unit un
     WHERE sh.slunit = un.un_name;

CREATE VIEW shoelace AS
    SELECT s.sl_name,
           s.sl_avail,
           s.sl_color,
           s.sl_len,
           s.sl_unit,
           s.sl_len * u.un_fact AS sl_len_cm
      FROM shoelace_data s, unit u
     WHERE s.sl_unit = u.un_name;

CREATE VIEW shoe_ready AS
    SELECT rsh.shoename,
           rsh.sh_avail,
           rsl.sl_name,
           rsl.sl_avail,
           least(rsh.sh_avail, rsl.sl_avail) AS total_avail
      FROM shoe rsh, shoelace rsl
     WHERE rsl.sl_color = rsh.slcolor
       AND rsl.sl_len_cm >= rsh.slminlen_cm
       AND rsl.sl_len_cm <= rsh.slmaxlen_cm;

El comando CREATE VIEW para la vista shoelace (que es la más simple que tenemos) creará una relación shoelace y una entrada en pg_rewrite que indica que hay una regla de reescritura que debe aplicarse cada vez que se haga referencia a la relación shoelace en la tabla de rangos de una consulta. La regla no tiene condición de regla (qualification) (que se analizará más adelante, con las reglas que no son de tipo SELECT, ya que las reglas SELECT actualmente no pueden tenerlas) y es INSTEAD. Ten en cuenta que las condiciones de regla no son lo mismo que las condiciones de consulta (query qualifications). La acción de nuestra regla tiene una condición de consulta. La acción de la regla es un árbol de consulta que es una copia de la sentencia SELECT en el comando de creación de la vista.

Ahora rellenamos unit, shoe_data y shoelace_data y ejecutamos una consulta simple en una vista:

INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);

INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');

INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');

SELECT * FROM shoelace;

 sl_name   | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
-----------+----------+----------+--------+---------+-----------
 sl1       |        5 | black    |     80 | cm      |        80
 sl2       |        6 | black    |    100 | cm      |       100
 sl7       |        7 | brown    |     60 | cm      |        60
 sl3       |        0 | black    |     35 | inch    |      88.9
 sl4       |        8 | black    |     40 | inch    |     101.6
 sl8       |        1 | brown    |     40 | inch    |     101.6
 sl5       |        4 | brown    |      1 | m       |       100
 sl6       |        0 | brown    |    0.9 | m       |        90
(8 rows)

Este es el SELECT más simple que puedes hacer en nuestras vistas, por lo que aprovechamos esta oportunidad para explicar los conceptos básicos de las reglas de vistas. El comando SELECT * FROM shoelace fue interpretado por el analizador (parser) y produjo el árbol de consulta:

SELECT shoelace.sl_name, shoelace.sl_avail,
       shoelace.sl_color, shoelace.sl_len,
       shoelace.sl_unit, shoelace.sl_len_cm
  FROM shoelace shoelace;

y este se entrega al sistema de reglas. El sistema de reglas recorre la tabla de rangos y comprueba si hay reglas para alguna relación. Al procesar la entrada de la tabla de rangos para shoelace (la única hasta ahora), encuentra la regla _RETURN con el árbol de consulta:

SELECT s.sl_name, s.sl_avail,
       s.sl_color, s.sl_len, s.sl_unit,
       s.sl_len * u.un_fact AS sl_len_cm
  FROM shoelace old, shoelace new,
       shoelace_data s, unit u
 WHERE s.sl_unit = u.un_name;

Para expandir la vista, el reescritor simplemente crea una entrada de tabla de rangos de subconsulta que contiene el árbol de consulta de la acción de la regla, y sustituye esta entrada de tabla de rangos por la original que hacía referencia a la vista. El árbol de consulta reescrito resultante es casi el mismo que si hubieras escrito:

SELECT shoelace.sl_name, shoelace.sl_avail,
       shoelace.sl_color, shoelace.sl_len,
       shoelace.sl_unit, shoelace.sl_len_cm
  FROM (SELECT s.sl_name,
               s.sl_avail,
               s.sl_color,
               s.sl_len,
               s.sl_unit,
               s.sl_len * u.un_fact AS sl_len_cm
          FROM shoelace_data s, unit u
         WHERE s.sl_unit = u.un_name) shoelace;

Sin embargo, hay una diferencia: la tabla de rangos de la subconsulta tiene dos entradas adicionales: shoelace old y shoelace new. Estas entradas no participan directamente en la consulta, ya que no están referenciadas por el árbol de unión (join tree) ni por la lista de objetivos de la subconsulta. El reescritor las utiliza para almacenar la información de comprobación de privilegios de acceso que estaba originalmente presente en la entrada de la tabla de rangos que hacía referencia a la vista. De esta manera, el ejecutor seguirá comprobando que el usuario tenga los privilegios adecuados para acceder a la vista, aunque no haya un uso directo de la vista en la consulta reescrita.

Esa fue la primera regla aplicada. El sistema de reglas continuará comprobando las entradas restantes de la tabla de rangos en la consulta superior (en este ejemplo no hay más), y comprobará recursivamente las entradas de la tabla de rangos en la subconsulta añadida para ver si alguna de ellas hace referencia a vistas. (¡Pero no expandirá old ni new, de lo contrario tendríamos una recursión infinita!). En este ejemplo, no hay reglas de reescritura para shoelace_data ni para unit, por lo que la reescritura está completa y el resultado anterior es el que finalmente se entrega al planificador.

Ahora queremos escribir una consulta que averigüe para qué zapatos actualmente en la tienda tenemos los cordones correspondientes (color y longitud) y donde el número total de pares exactamente coincidentes sea mayor o igual a dos.

SELECT * FROM shoe_ready WHERE total_avail >= 2;

 shoename | sh_avail | sl_name | sl_avail | total_avail
----------+----------+---------+----------+-------------
 sh1      |        2 | sl1     |        5 |           2
 sh3      |        4 | sl7     |        7 |           4
(2 rows)

La salida del analizador (parser) esta vez es el árbol de consulta:

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM shoe_ready shoe_ready
 WHERE shoe_ready.total_avail >= 2;

La primera regla aplicada será la de la vista shoe_ready y da como resultado el árbol de consulta:

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM (SELECT rsh.shoename,
               rsh.sh_avail,
               rsl.sl_name,
               rsl.sl_avail,
               least(rsh.sh_avail, rsl.sl_avail) AS total_avail
          FROM shoe rsh, shoelace rsl
         WHERE rsl.sl_color = rsh.slcolor
           AND rsl.sl_len_cm >= rsh.slminlen_cm
           AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail >= 2;

Del mismo modo, las reglas para shoe y shoelace se sustituyen en la tabla de rangos de la subconsulta, lo que da lugar a un árbol de consulta final de tres niveles:

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM (SELECT rsh.shoename,
               rsh.sh_avail,
               rsl.sl_name,
               rsl.sl_avail,
               least(rsh.sh_avail, rsl.sl_avail) AS total_avail
          FROM (SELECT sh.shoename,
                       sh.sh_avail,
                       sh.slcolor,
                       sh.slminlen,
                       sh.slminlen * un.un_fact AS slminlen_cm,
                       sh.slmaxlen,
                       sh.slmaxlen * un.un_fact AS slmaxlen_cm,
                       sh.slunit
                  FROM shoe_data sh, unit un
                 WHERE sh.slunit = un.un_name) rsh,
               (SELECT s.sl_name,
                       s.sl_avail,
                       s.sl_color,
                       s.sl_len,
                       s.sl_unit,
                       s.sl_len * u.un_fact AS sl_len_cm
                  FROM shoelace_data s, unit u
                 WHERE s.sl_unit = u.un_name) rsl
         WHERE rsl.sl_color = rsh.slcolor
           AND rsl.sl_len_cm >= rsh.slminlen_cm
           AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail > 2;

Esto podría parecer ineficiente, pero el planificador colapsará esto en un árbol de consulta de un solo nivel «subiendo» (pulling up) las subconsultas, y luego planificará las uniones (joins) tal como si las hubiéramos escrito manualmente. Por lo tanto, colapsar el árbol de consulta es una optimización de la que el sistema de reescritura no tiene que preocuparse.

Dos detalles del árbol de consulta no se tocan en la descripción de las reglas de vistas anterior. Estos son el tipo de comando y la relación de resultado. De hecho, el tipo de comando no es necesario para las reglas de vistas, pero la relación de resultado puede afectar la forma en que funciona el reescritor de consultas, ya que se debe tener especial cuidado si la relación de resultado es una vista.

Solo hay unas pocas diferencias entre un árbol de consulta para un SELECT y uno para cualquier otro comando. Obviamente, tienen un tipo de comando diferente y, para un comando que no sea un SELECT, la relación de resultado apunta a la entrada de la tabla de rangos a donde debe ir el resultado. Todo lo demás es absolutamente idéntico. Por lo tanto, teniendo dos tablas t1 y t2 con columnas a y b, los árboles de consulta para las dos sentencias:

SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;

UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;

son casi idénticos. En particular:

La consecuencia es que ambos árboles de consulta dan como resultado planes de ejecución similares: ambos son uniones (joins) sobre las dos tablas. Para el UPDATE, el planificador añade las columnas faltantes de t1 a la lista de objetivos y el árbol de consulta final se leerá como:

UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;

y así, la ejecución sobre la unión producirá exactamente el mismo conjunto de resultados que:

SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;

Pero hay un pequeño problema en UPDATE: a la parte del plan del ejecutor que realiza la unión no le importa para qué sirven los resultados de la unión. Simplemente produce un conjunto de resultados de filas. El hecho de que uno sea un comando SELECT y el otro sea un UPDATE se maneja más arriba en el ejecutor, donde se sabe que este es un UPDATE, y se sabe que este resultado debe ir a la tabla t1. Pero, ¿cuál de las filas que están allí tiene que ser reemplazada por la nueva fila?

Para resolver este problema, se añade otra entrada a la lista de objetivos en las sentencias UPDATE (y también en DELETE): el ID de la tupla actual (CTID). Esta es una columna del sistema que contiene el número de bloque de archivo y la posición en el bloque para la fila. Conociendo la tabla, el CTID se puede utilizar para recuperar la fila original de t1 que se va a actualizar. Después de añadir el CTID a la lista de objetivos, la consulta en realidad se ve así:

SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;

Ahora entra en escena otro detalle de PostgreSQL. Las filas antiguas de las tablas no se sobrescriben, y es por eso que ROLLBACK es rápido. In un UPDATE, la nueva fila de resultado se inserta en la tabla (después de quitar el CTID) y en la cabecera de la fila antigua, a la que apuntaba el CTID, las entradas cmax y xmax se establecen al contador de comandos actual y al ID de la transacción actual. De este modo, la fila antigua queda oculta y, después de que la transacción se confirma (commits), el recolector de basura (vacuum) eventualmente puede eliminar la fila muerta.

Sabiendo todo esto, podemos simplemente aplicar las reglas de las vistas exactamente de la misma manera a cualquier comando. No hay diferencia.

Lo anterior demuestra cómo el sistema de reglas incorpora las definiciones de las vistas en el árbol de consulta original. En el segundo ejemplo, un simple SELECT de una vista creó un árbol de consulta final que es una unión (join) de 4 tablas (unit se usó dos veces con nombres diferentes).

El beneficio de implementar vistas con el sistema de reglas es que el planificador tiene toda la información sobre qué tablas deben escanearse, además de las relaciones entre estas tablas, más las condiciones (qualifications) restrictivas de las vistas, más las condiciones de la consulta original en un único árbol de consulta. Y esta sigue siendo la situación cuando la consulta original ya es una unión sobre vistas. El planificador tiene que decidir cuál es el mejor camino para ejecutar la consulta, y cuanta más información tenga el planificador, mejor será esta decisión. Y el sistema de reglas tal como está implementado en PostgreSQL asegura que esta sea toda la información disponible sobre la consulta hasta ese punto.

¿Qué sucede si una vista se indica como la relación de destino para un INSERT, UPDATE, DELETE o MERGE? Realizar las sustituciones descritas anteriormente daría un árbol de consulta en el que la relación de resultado apunta a una entrada de la tabla de rangos de subconsulta, lo cual no funcionará. Sin embargo, hay varias formas en las que PostgreSQL puede admitir la apariencia de actualizar una vista. En orden de complejidad para el usuario, estas son: sustituir automáticamente la vista por la tabla subyacente, ejecutar un disparador (trigger) definido por el usuario, o reescribir la consulta según una regla definida por el usuario. Estas opciones se analizan a continuación.

Si la subconsulta selecciona de una única relación base y es lo suficientemente simple, el reescritor puede reemplazar automáticamente la subconsulta con la relación base subyacente de modo que el INSERT, UPDATE, DELETE o MERGE se aplique a la relación base de la manera apropiada. Las vistas que son “lo suficientemente simples” para esto se llaman actualizables automáticamente. Para obtener información detallada sobre los tipos de vistas que se pueden actualizar automáticamente, consulta CREATE VIEW.

Alternativamente, la operación puede ser manejada por un disparador (trigger) INSTEAD OF proporcionado por el usuario en la vista (consulta CREATE TRIGGER). La reescritura funciona de manera ligeramente diferente en este caso. Para INSERT, el reescritor no hace nada en absoluto con la vista, dejándola como la relación de resultado para la consulta. Para UPDATE, DELETE y MERGE, todavía es necesario expandir la consulta de la vista para producir las filas “old” (antiguas) que el comando intentará actualizar, eliminar o combinar (merge). Por lo tanto, la vista se expande de manera normal, pero se añade otra entrada de tabla de rangos sin expandir a la consulta para representar a la vista en su función como la relación de resultado.

El problema que surge ahora es cómo identificar las filas que se van a actualizar en la vista. Recuerda que cuando la relación de resultado es una tabla, se añade una entrada CTID especial a la lista de objetivos para identificar las ubicaciones físicas de las filas a actualizar. Esto no funciona si la relación de resultado es una vista, porque una vista no tiene ningún CTID, ya que sus filas no tienen ubicaciones físicas reales. En su lugar, para una operación UPDATE, DELETE o MERGE, se añade una entrada wholerow especial a la lista de objetivos, que se expande para incluir todas las columnas de la vista. El ejecutor utiliza este valor para proporcionar la fila “old” al disparador (trigger) INSTEAD OF. Depende del disparador (trigger) descifrar qué actualizar basándose en los valores de la fila antigua y nueva.

Otra posibilidad es que definas reglas INSTEAD que especifiquen acciones de sustitución para los comandos INSERT, UPDATE y DELETE en una vista. Estas reglas reescribirán el comando, normalmente en un comando que actualiza una o más tablas, en lugar de vistas. Ese es el tema de Section 39.4. Ten en cuenta que esto no funcionará con MERGE, que actualmente no admite reglas en la relación de destino que no sean reglas SELECT.

Ten en cuenta que las reglas se evalúan primero, reescribiendo la consulta original antes de que sea planificada y ejecutada. Por lo tanto, si una vista tiene disparadores (triggers) INSTEAD OF, así como reglas en INSERT, UPDATE o DELETE, las reglas se evaluarán primero y, dependiendo del resultado, puede que los disparadores (triggers) no se utilicen en absoluto.

La reescritura automática de una consulta INSERT, UPDATE, DELETE o MERGE en una vista simple siempre se intenta en último lugar. Por lo tanto, si una vista tiene reglas o disparadores (triggers), estos anularán el comportamiento predeterminado de las vistas actualizables automáticamente.

Si no hay reglas INSTEAD ni disparadores (triggers) INSTEAD OF para la vista, y el reescritor no puede reescribir automáticamente la consulta como una actualización en la relación base subyacente, se lanzará un error porque el ejecutor no puede actualizar una vista como tal.