Las vistas en Postgres se implementan utilizando el sistema de reglas. De hecho, no hay diferencia entre
CREATE VIEW myview AS SELECT * FROM mytab; |
CREATE TABLE myview (la misma lista de atributos de mytab); CREATE RULE "_RETmyview" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; |
Las reglas ON SELECT se aplican a todas las queries como el �ltimo paso, incluso si el comando dado es INSERT, UPDATE o DELETE. Y tienen diferentes semanticas de las otras en las que modifican el arbol de traducci�n en lugar de crear uno nuevo. Por ello, las reglas SELECT se describen las primeras.
Actualmente, debe haber s�lo una acci�n y debe ser una acci�n SELECT que es una INSTEAD. Esta restricci�n se requer�a para hacer las reglas seguras contra la apertura por usuarios ordinarios, y restringe las reglas ON SELECT a reglas para vistas reales.
El ejemplo para este documento son dos vistas unidas que hacen algunos c�lculos y algunas otras vistas utilizadas para ello. Una de estas dos primeras vistas se personaliza m�s tarde a�adiendo reglas para operaciones de INSERT, UPDATE y DELETE de modo que el resultado final ser� una vista que se comporta como una tabla real con algunas funcionalidades m�gicas. No es un ejemplo f�cil para empezar, y quiz� sea demasiado duro. Pero es mejor tener un ejemplo que cubra todos los puntos discutidos paso a paso que tener muchos ejemplos diferentes que tener que mezclar despu�s.
La base de datos necesitada para ejecutar los ejemplos se llama al_bundy. Ver� pronto el porqu� de este nombre. Y necesita tener instalado el lenguaje procedural PL/pgSQL, ya que necesitaremos una peque�a funci�n min() que devuelva el menor de dos valores enteros. Creamos esta funci�n como:
CREATE FUNCTION min(integer, integer) RETURNS integer AS 'BEGIN IF $1 < $2 THEN RETURN $1; END IF; RETURN $2; END;' LANGUAGE 'plpgsql'; |
Las tablas reales que necesitaremos en las dos primeras descripciones del sistema de reglas son estas:
CREATE TABLE shoe_data ( -- datos de zapatos shoename char(10), -- clave primaria (primary key) sh_avail integer, -- n�mero de pares utilizables slcolor char(10), -- color de cord�n preferido slminlen float, -- longitud m�nima de cord�n slmaxlen float, -- longitud m�xima del cord�n slunit char(8) -- unidad de longitud ); CREATE TABLE shoelace_data ( -- datos de cordones de zapatos sl_name char(10), -- clave primaria (primary key) sl_avail integer, -- n�mero de pares utilizables sl_color char(10), -- color del cord�n sl_len float, -- longitud del cord�n sl_unit char(8) -- unidad de longitud ); CREATE TABLE unit ( -- unidades de longitud un_name char(8), -- clave primaria (primary key) un_fact float -- factor de transformaci�n a cm ); |
Las vistas las crearemos 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, min(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; |
La acci�n de las reglas es un �rbol de query que es una copia exacta de la instrucci�n SELECT en el comando de creaci�n de la vista.
Nota: | |
---|---|
Las dos tablas de rango extra para NEW y OLD (llamadas *NEW* y *CURRENT* por razones hist�ricas en el �rbol de query escrito) que se pueden ver en la entrada pg_rewrite no son de interes para las reglas de SELECT. |
al_bundy=> INSERT INTO unit VALUES ('cm', 1.0); al_bundy=> INSERT INTO unit VALUES ('m', 100.0); al_bundy=> INSERT INTO unit VALUES ('inch', 2.54); al_bundy=> al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh1', 2, 'black', 70.0, 90.0, 'cm'); al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh2', 0, 'black', 30.0, 40.0, 'inch'); al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); al_bundy=> al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl1', 5, 'black', 80.0, 'cm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl2', 6, 'black', 100.0, 'cm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl3', 0, 'black', 35.0 , 'inch'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl4', 8, 'black', 40.0 , 'inch'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl5', 4, 'brown', 1.0 , 'm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl6', 0, 'brown', 0.9 , 'm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl7', 7, 'brown', 60 , 'cm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl8', 1, 'brown', 40 , 'inch'); al_bundy=> al_bundy=> 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) |
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace; |
SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, float8mul(s.sl_len, u.un_fact) AS sl_len_cm FROM shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u WHERE bpchareq(s.sl_unit, u.un_name); |
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace, shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u; |
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace, shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u WHERE bpchareq(s.sl_unit, u.un_name); |
SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, float8mul(s.sl_len, u.un_fact) AS sl_len_cm FROM shoelace shoelace, shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u WHERE bpchareq(s.sl_unit, u.un_name); |
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; |
Ahora enfrentamos a Al al problema de que los Blues Brothers aparecen en su tienda y quieren comprarse zapatos nuevos, y como son los Blues Brothers, quieren llevar los mismos zapatos. Y los quieren llevar inmediatamente, de modo que necesitan tambi�n cordones.
Al necesita conocer los zapatos para los que tiene en el almac�n cordones en este momento (en color y en tama�o), y adem�s para los que tenga un n�mero igual o superior a 2. Nosotros le ense�amos a realizar la consulta a su base de datos:
al_bundy=> 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 traductor es esta vez el arbol de traducci�n.
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 int4ge(shoe_ready.total_avail, 2); |
SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, min(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe_ready shoe_ready, shoe_ready *OLD*, shoe_ready *NEW*, shoe rsh, shoelace rsl WHERE int4ge(min(rsh.sh_avail, rsl.sl_avail), 2) AND (bpchareq(rsl.sl_color, rsh.slcolor) AND float8ge(rsl.sl_len_cm, rsh.slminlen_cm) AND float8le(rsl.sl_len_cm, rsh.slmaxlen_cm) ); |
SELECT sh.shoename, sh.sh_avail, rsl.sl_name, rsl.sl_avail, min(sh.sh_avail, rsl.sl_avail) AS total_avail, FROM shoe_ready shoe_ready, shoe_ready *OLD*, shoe_ready *NEW*, shoe rsh, shoelace rsl, shoe *OLD*, shoe *NEW*, shoe_data sh, unit un WHERE (int4ge(min(sh.sh_avail, rsl.sl_avail), 2) AND (bpchareq(rsl.sl_color, sh.slcolor) AND float8ge(rsl.sl_len_cm, float8mul(sh.slminlen, un.un_fact)) AND float8le(rsl.sl_len_cm, float8mul(sh.slmaxlen, un.un_fact)) ) ) AND bpchareq(sh.slunit, un.un_name); |
SELECT sh.shoename, sh.sh_avail, s.sl_name, s.sl_avail, min(sh.sh_avail, s.sl_avail) AS total_avail FROM shoe_ready shoe_ready, shoe_ready *OLD*, shoe_ready *NEW*, shoe rsh, shoelace rsl, shoe *OLD*, shoe *NEW*, shoe_data sh, unit un, shoelace *OLD*, shoelace *NEW*, shoelace_data s, unit u WHERE ( (int4ge(min(sh.sh_avail, s.sl_avail), 2) AND (bpchareq(s.sl_color, sh.slcolor) AND float8ge(float8mul(s.sl_len, u.un_fact), float8mul(sh.slminlen, un.un_fact)) AND float8le(float8mul(s.sl_len, u.un_fact), float8mul(sh.slmaxlen, un.un_fact)) ) ) AND bpchareq(sh.slunit, un.un_name) ) AND bpchareq(s.sl_unit, u.un_name); |
SELECT sh.shoename, sh.sh_avail, s.sl_name, s.sl_avail, min(sh.sh_avail, s.sl_avail) AS total_avail FROM shoe_data sh, shoelace_data s, unit u, unit un WHERE min(sh.sh_avail, s.sl_avail) >= 2 AND s.sl_color = sh.slcolor AND s.sl_len * u.un_fact >= sh.slminlen * un.un_fact AND s.sl_len * u.un_fact <= sh.slmaxlen * un.un_fact AND sh.sl_unit = un.un_name AND s.sl_unit = u.un_name; |
Nota | |
---|---|
Actualmente no hay mecanismos de parar la recursi�n para las reglas de las vistas en el sistema de reglas (s�lo para las otras reglas). Esto no es muy grave, ya que la �nica forma de meterlo en un bucle sin fin (bloqueando al cliente hasta que lea el limite de memoria) es crear tablas y luego crearles reglas a mano con CREATE RULE de forma que una lea a la otra y la otra a la una. Esto no puede ocurrir con el comando CREATE VIEW, porque en la primera creaci�n de una vista la segunda a�n no existe, de modo que la primera vista no puede seleccionar desde la segunda. |
Dos detalles del arbol de traducci�n no se han tocado en la descripci�n de las reglas de vistas hasta ahora. Estos son el tipo de comando (commandtype) y la relaci�n resultado (resultrelation). De hecho, las reglas de vistas no necesitan estas informaciones.
Hay s�lo unas pocas diferencias entre un �rbol de traducci�n para una SELECT y uno para cualquier otro comando. Obviamente, tienen otros tipos de comandos, y esta vez la relaci�n resultado apunta a la entrada de la tabla de rango donde ir� el resultado. Cualquier otra cosa es absolutamente igual. Por ello, teniendo dos tablas t1 y t2, con atributos a y b, los �rboles de traducci�n para las dos instrucciones:
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; UPDATE t1 SET b = t2.b WHERE t1.a = t2.a; |
Las tablas de rango contienen entradas para las tablas t1 y t2.
Las listas objetivo continen una variable que apunta al atributo b de la entrada de la tabla rango para la tabla t2.
Las expresiones de cualificaci�n comparan los atributos a de ambos rangos para la igualdad.
UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a; |
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; |
Para resolver este problema, se a�ade otra entrada a la lista objetivo en las instrucciones UPDATE y DELETE. Es el identificador de tupla actual (current tuple id, ctid). Este es un atributo de sistema con caracter�sticas especiales. Contiene el bloque y posici�n en el bloque para cada fila. Conociendo la tabla, el ctid puede utilizarse para encontrar una fila espec�fica en una tabla de 1.5 GB que contiene millones de filas atacando un �nico bloque de datos. Tras la adici�n del ctid a la lista objetivo, el juego de resultados final se podr�a definir como
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; |
Conociendo todo eso, podemos simplemente aplicar las reglas de las vistas exactamente en la misma forma en cualquier comando. No hay diferencia.
Todo lo anterior demuestra como el sistema de reglas incorpora las definiciones de las vistas en el �rbol de traducci�n original. En el segundo ejemplo, una simple SELECT de una vista cre� un �rbol de traducci�n final que es una join de cuatro tablas (cada una se utiliza dos veces con diferente nombre).
Los beneficios de implementar las vistas con el sistema de reglas est�n en que el optimizados tiene toda la informaci�n sobre qu� tablas tienen que ser revisadas, m�s las relaciones entre estas tablas, m�s las cualificaciones restrictivas a partir de la definici�n de las vistas, m�s las cualificaciones de la query original, todo en un �nico �rbol de traducci�n. Y esta es tambi�n la situaci�n cuando la query original es ya una join entre vistas. Ahora el optimizador debe decidir cu�l es la mejor ruta para ejecutar la query. Cuanta m�s informaci�n tenga el optimizador, mejor ser� la decisi�n. Y la forma en que se implementa el sistema de reglas en Postgres asegura que toda la informaci�n sobre la query est� utilizable.
Hubo un tiempo en el que el sistema de reglas de Postgres se consideraba agotado. El uso de reglas no se recomendaba, y el �nico lugar en el que trabajaban era las reglas de las vistas. E incluso estas reglas de las vistas daban problemas porque el sistema de reglas no era capaz de aplicarse adecuadamente en m�s instrucciones que en SELECT (por ejemplo, no trabajar�a en una UPDATE que utilice datos de una vista).
Durante ese tiempo, el desarrollo se dirigi� hacia muchas caracter�sticas a�adidas al traductor y al optimizador. El sistema de reglas fu� quedando cada vez m�s desactualizado en sus capacidades, y se volvi� cada vez m�s dificil de actualizar. Y por ello, nadie lo hizo.
En 6.4, alguien cerr� la puerta, respir� hondo, y se puso manos a la obra. El resultado fu� el sistema de reglas cuyas capacidades se han descrito en este documento. Sin embargo, hay todav�a algunas construcciones no manejadas, y algunas fallan debido a cosas que no son soportadas por el optimizador de queries de Postgres.
Las vistas con columnas agregadas tienen malos problemas. Las expresiones agregadas en las cualificaciones deben utilizarse en subselects. Actualmente no es posible hacer una join de dos vistas en las que cada una de ellas tenga una columna agregada, y comparar los dos valores agregados en a cualificaci�n. Mientras tanto, es posible colocar estas expresiones agregadas en funciones con los argumentos apropiados y utilizarlas en la definici�n de las vistas.
Las vistas de uniones no son soportadas. Ciertamente es sencillo reescribir una SELECT simple en una uni�n, pero es un poco m�s dificil si la vista es parte de una join que hace una UPDATE.
Las clausulas ORDER BY en las definiciones de las vistas no est�n soportadas.
DISTINCT no est� soportada en las definiciones de vistas.
La utilizaci�n del sistema de reglas descrito para implementar las vistas tiene algunos efectos colaterales divertidos. Lo siguiente no parece trabajar:
al_bundy=> INSERT INTO shoe (shoename, sh_avail, slcolor) al_bundy-> VALUES ('sh5', 0, 'black'); INSERT 20128 1 al_bundy=> SELECT shoename, sh_avail, slcolor FROM shoe_data; shoename |sh_avail|slcolor ----------+--------+---------- sh1 | 2|black sh3 | 4|brown sh2 | 0|black sh4 | 3|brown (4 rows) |
Podemos tambi�n intentar una DELETE, y si no tiene una cualificaci�n, nos dir� que las filas se han borrado y la siguiente ejecuci�n de vacuum limpiar� el fichero hasta tama�o cero.
La razon para este comportamiento es que el �rbol de la traducci�n para la INSERT no hace referencia a la relaci�n shoe en ninguna variable. La lista objetivo contiene s�lo valores constantes. Por ello no hay reglas que aplicar y se mantiene sin cambiar hasta la ejecuci�n, insertandose la fila. Del mismo modo para la DELETE.
Para cambiar esto, podemos definir reglas que modifiquen el comportamiento de las queries no-SELECT. Este es el tema de la siguiente secci�n.