Documentation PostgreSQL 7.4.29 | ||||
---|---|---|---|---|
Pr�c�dent | Arri�re rapide | Chapitre 34. Syst�me de r�gles | Avance rapide | Suivant |
Avec PostgreSQL, les vues sont impl�ment�es en utilisant le syst�me de r�gles. En fait, il n'y a essentiellement pas de diff�rences entre
CREATE VIEW ma_vue AS SELECT * FROM ma_table;
et ces deux commandes
CREATE TABLE ma_vue (liste de colonnes identique � celle de ma_table); CREATE RULE "_RETURN" AS ON SELECT TO ma_vue DO INSTEAD SELECT * FROM ma_table;
parce que c'est exactement ce que fait la commande CREATE VIEW en interne. Ceci a quelques effets de bord. L'un d'entre eux est que l'information sur une vue dans les catalogues syst�me PostgreSQL est exactement le m�me que celui d'une table. Donc, pour l'analyseur, il n'y a aucune diff�rence entre une table et une vue. Elles sont la m�me chose : des relations.
Les r�gles ON SELECT sont appliqu�es � toutes les requ�tes comme la derni�re �tape, m�me si la commande donn�e est un INSERT, UPDATE ou DELETE. Et ils ont des s�mantiques diff�rentes � partir des r�gles sur les autres types de commandes dans le fait qu'elles modifient l'arbre de requ�tes en place au lieu d'en cr�er un nouveau. Donc, les r�gles SELECT sont d�crites avant.
Actuellement, il n'existe qu'une action dans une r�gle ON SELECT et elle doit �tre une action SELECT inconditionnelle qui est INSTEAD. Cette restriction �tait requise pour rendre les r�gles assez s�res pour les ouvrir aux utilisateurs ordinaires et cela restreint les r�gles ON SELECT � de vraies r�gles de vues.
Pour ce chapitre, les exemples sont deux vues jointes r�alisant quelques calculs et quelques vues suppl�mentaires les utilisant � leur tour. Une des deux premi�res vues est personnalis�e plus tard en ajoutant des r�gles pour des op�rations INSERT, UPDATE et DELETE de fa�on � ce que le r�sultat final sera une vue qui se comporte comme une vraie table avec quelques fonctionnalit�s magiques. Il n'existe pas un tel exemple pour commencer et ceci rend les choses plus difficiles � obtenir. Mais il est mieux d'avoir un exemple couvrant tous les points discut�s �tape par �tape plut�t que plusieurs exemples, rendant la compr�hension plus difficile.
Pour cet exemple, nous avons besoin d'une petite fonction min renvoyant la valeur la plus basse entre deux entiers. Nous la cr�ons ainsi
CREATE FUNCTION min(integer, integer) RETURNS integer AS ' SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END ' LANGUAGE SQL STRICT;
Les tables r�elles dont nous avons besoin dans les deux premi�res descriptions du syst�me de r�gles sont les suivantes :
CREATE TABLE donnees_chaussure ( nom_chaussure text, -- cl� primaire dispo_chaussure integer, -- nombre de pairs disponibles couleur_chaussure text, -- couleur de lacet pr�f�r�e long_min_chaussure real, -- longueur minimum du lacet long_max_chaussure real, -- longueur maximum du lacet unite_long_chaussure text -- unit� de longueur ); CREATE TABLE donnees_lacet ( nom_lacet text, -- cl� primaire dispo_lacet integer, -- nombre de pairs disponibles couleur_lacet text, -- couleur du lacet longueur_lacet real, -- longueur du lacet unite_lacet text -- unit� de longueur ); CREATE TABLE unite ( nom_unite text, -- cl� primaire facteur_unite real -- facteur pour le transformer en cm );
Comme vous pouvez le constater, elles repr�sentent les donn�es d'un magasin de chaussures.
Les vues sont cr��es avec
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 commande CREATE VIEW pour la vue shoelace (qui est la plus simple que nous avons) �crira une relation shoelace et une entr�e dans pg_rewrite indiquant la pr�sence d'une r�gle de r��criture devant �tre appliqu�e � chaque fois que la relation shoelace est r�f�renc�e dans une table d'�chelle de la requ�te. La r�gle n'a aucune qualification de r�gle (discut� plus tard, avec les r�gles non SELECT car les r�gles SELECT ne le ont pas encore) et qu'il s'agit de INSTEAD. Notez que les qualifications de r�gles ne sont pas identiques aux qualifications de requ�tes. L'action de notre r�gle a une qualification de requ�te. L'action de la r�gle a un arbre de requ�te qui est une copie de l'instruction SELECT dans la commande de cr�ation de la vue.
Note�: Les deux entr�es suppl�mentaires de la table d'�chelle pour NEW et OLD (nomm�es *NEW* et *OLD* pour des raisons historiques dans l'arbre de requ�te affich�) que vous pouvez voir dans l'entr�e de pg_rewrite ne sont d'aucun int�r�t pour les r�gles SELECT.
Maintenant, nous remplissons unit, donnees_chaussure et donnees_lacet, puis nous lan�ons une requ�te simple sur une vue :
INSERT INTO unit VALUES ('cm', 1.0); INSERT INTO unit VALUES ('m', 100.0); INSERT INTO unit VALUES ('inch', 2.54); INSERT INTO donnees_chaussure VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); INSERT INTO donnees_chaussure VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); INSERT INTO donnees_chaussure VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); INSERT INTO donnees_chaussure VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); INSERT INTO donnees_lacet VALUES ('sl1', 5, 'black', 80.0, 'cm'); INSERT INTO donnees_lacet VALUES ('sl2', 6, 'black', 100.0, 'cm'); INSERT INTO donnees_lacet VALUES ('sl3', 0, 'black', 35.0 , 'inch'); INSERT INTO donnees_lacet VALUES ('sl4', 8, 'black', 40.0 , 'inch'); INSERT INTO donnees_lacet VALUES ('sl5', 4, 'brown', 1.0 , 'm'); INSERT INTO donnees_lacet VALUES ('sl6', 0, 'brown', 0.9 , 'm'); INSERT INTO donnees_lacet VALUES ('sl7', 7, 'brown', 60 , 'cm'); INSERT INTO donnees_lacet VALUES ('sl8', 1, 'brown', 40 , 'inch'); SELECT * FROM lacet; nom_lacet | dispo_lacet | couleur_lacet | longueur_lacet | unite_lacet | longueur_lacet_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)
C'est la requ�te SELECT la plus simple que vous pouvez lancer sur nos vues, donc nous prenons cette opportunit� d'expliquer les bases des r�gles de vues. SELECT * FROM lacet a �t� interpr�t� par l'analyseur et a produit l'arbre de requ�te
SELECT lacet.nom_lacet, lacet.dispo_lacet, lacet.couleur_lacet, lacet.longueur_lacet, lacet.unite_lacet, lacet.longueur_lacet_cm FROM lacet lacet;
et ceci est donn� au syst�me de r�gles. Ce syst�me traverse la table d'�chelle et v�rifie s'il existe des r�gles pour chaque relation. Lors du traitement d'une entr�e de la table d'�chelle pour lacet (la seule jusqu'� maintenant), il trouve la r�gle _RETURN avec l'arbre de requ�te
SELECT s.nom_lacet, s.dispo_lacet, s.couleur_lacet, s.longueur_lacet, s.unite_lacet, s.longueur_lacet * u.facteur_unite AS longueur_lacet_cm FROM lacet *OLD*, lacet *NEW*, donnees_lacet s, unit u WHERE s.unite_lacet = u.nom_unite;
Pour �tendre la vue, la r��criture cr�e simplement une entr�e de la table d'�chelle de sous-requ�te contenant l'arbre de requ�te de l'action de la r�gle et substitue cette entr�e avec l'original r�f�renc� dans la vue. L'arbre d'�chelle r�sultant de la r��criture est pratiquement identique � celui que vous avez saisi
SELECT lacet.nom_lacet, lacet.dispo_lacet, lacet.couleur_lacet, lacet.longueur_lacet, lacet.unite_lacet, lacet.longueur_lacet_cm FROM (SELECT s.nom_lacet, s.dispo_lacet, s.couleur_lacet, s.longueur_lacet, s.unite_lacet, s.longueur_lacet * u.facteur_unite AS longueur_lacet_cm FROM donnees_lacet s, unit u WHERE s.unite_lacet = u.nom_unite) lacet;
N�anmoins, il y a une diff�rence : la table d'�chelle de la sous-requ�te a deux entr�es suppl�mentaires, lacet *OLD* et lacet *NEW*. Ces entr�es ne participent pas directement dans la requ�te car elles ne sont pas r�f�renc�es par l'arbre de jointure de la sous-requ�te ou par la liste cible. La r��criture les utilise pour enregistrer l'information de v�rification des droits d'acc�s qui �taient pr�sents � l'origine dans l'entr�e de table d'�chelle r�f�renc�e par la vue. De cette fa�on, l'ex�cution v�rifiera toujours que l'utilisateur a les bons droits pour acc�der � la vue m�me s'il n'y a pas d'utilisation directe de la vue dans la requ�te r��crite.
C'�tait la premi�re r�gle appliqu�e. Le syst�me de r�gles continuera de v�rifier les entr�es restantes de la table d'�chelle dans la requ�te principale (dans cet exemple, il n'en existe pas plus), et il v�rifiera r�cursivement les entr�es de la table d'�chelle dans la sous-requ�te ajout�e pour voir si une d'elle r�f�rence les vues. (Mais il n'�tendra ni *OLD* ni *NEW* --- sinon nous aurions une r�cursion infinie !) Dans cet exemple, il n'existe pas de r�gles de r��criture pour shoelace_data ou unit, donc la r��criture est termin�e et ce qui est ci-dessus est le r�sultat final donn� au planificateur.
Maintenant, nous voulons �crire une requ�te qui trouve les chaussures actuellement dans le magasin dont nous avons les lacets correspondant (couleur et longueur) et pour lesquels le nombre total de pairs correspondant exactement est sup�rieur ou �gal � deux.
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)
Cette fois, la sortie de l'analyseur est l'arbre de requ�te
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 premi�re r�gle appliqu�e sera celle de la vue shoe_ready et cela r�sultera avec cet arbre de requ�te
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, 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) shoe_ready WHERE shoe_ready.total_avail >= 2;
De fa�on similaire, les r�gles pour shoe et shoelace sont substitu�es dans la table d'�chelle de la sous-requ�te, amenant � l'arbre de requ�te final � trois niveaux :
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, min(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;
Il s'av�re que le planificateur r�duira cet arbre en un arbre de requ�te � deux niveaux : les commandes SELECT du bas seront <<�remont�es�>> dans le SELECT du milieu car il n'est pas n�cessaire de les traiter s�par�ment. Mais le SELECT du milieu restera s�par� du haut car il contient des fonctions d'agr�gat. Si nous les avions mont�, cela aurait modifi� le comportement du SELECT de haut niveau, ce qui n'est pas ce que nous voulons. N�anmoins, r�duire l'arbre de requ�te est une optimisation qui ne concerne pas le syst�me de r��criture.
Note�: Actuellement, il n'existe pas de m�canisme d'arr�ts de r�cursion pour les r�gles de vues dans le syst�me de r�gles (uniquement pour les autres types de r�gles). Ceci n'est pas trop g�nant car la seule fa�on de placer ce syst�me dans une boucle sans fin (massacrant le processus serveur jusqu'� ce qu'il atteigne la limite en m�moire) est de cr�er des tables et de configurer les r�gles de vue � la main avec CREATE RULE de telle fa�on que une s�lection � partir de l'autre qui s�lectionne � partir de la premi�re. C ela pourrait ne jamais arriver si CREATE VIEW est utilis� parce que pour le premier CREATE VIEW, la deuxi�me relation n'existera pas et, du coup, la premi�re vue ne pourra pas s�lectionner quelque chose de la seconde.
Deux d�tails de l'arbre de requ�te n'ont pas �t� abord�s dans la description des r�gles de vue ci-dessus. Ce sont le type de commande et le relation r�sultante. En fait, les r�gles de vue n'ont pas besoin de cette information.
Il existe seulement quelques diff�rences entre un arbre de requ�te pour un SELECT et un pour une autre commande. De fa�on �vidente, ils ont un type de commande diff�rent et pour une commande autre qu' unSELECT, la relation r�sultante pointe vers l'entr�e de table d'�chelle o� le r�sultat devrait arriver. Tout le reste est absolument identique. Donc, avec deux tables t1 et t2 avec les colonnes a et b, les arbres de requ�tes pour les deux commandes
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;
sont pratiquement identiques. En particulier :
Les tables d'�chelle contiennent des entr�es pour les tables t1 et t2.
Les listes cibles contiennent une variable pointant vers la colonne b de l'entr�e de la table d'�chelle pour la table t2.
Les expressions de qualification comparent les colonnes a des deux entr�es de table d'�chelle pour une �galit�.
Les arbres de jointure affichent une jointure simple entre t1 et t2.
La cons�quence est que les deux arbres de requ�te r�sultent en des plans d'ex�cution similaires : ce sont tous les deux des jointures sur les deux tables. Pour l'UPDATE, les colonnes manquantes de t1 sont ajout�es � la liste cible par le planificateur et l'arbre de requ�te final sera lu de cette fa�on
UPDATE t1 SET a = t1.a, b = t2.b WHERE t1.a = t2.a;
et, du coup, l'ex�cuteur lanc� sur la jointure produira exactement le m�me r�sultat qu'un
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
le ferait. Mais il y a un petit probl�me avec UPDATE : l'ex�cuteur ne fait pas attention au but du r�sultat de la jointure. Il produit simplement un ensemble de lignes composant le r�sultat. La diff�rence entre une commande SELECT et une commande UPDATE est g�r�e par celui qui a appel� l'ex�cuteur. L'appelant sait toujours (en regardant dans l'arbre de requ�tes) qu'il s'agit d'un UPDATE, et il sait que le r�sultat ira dans la table t1. Mais quelles lignes disponibles maintenant seront remplac�es par les nouvelles lignes ?
Pour r�soudre ce probl�me, une autre entr�e est ajout�e dans la liste cible de l'UPDATE (et aussi dans les instructions DELETE) : l'identifiant actuel du tuple (CTID, acronyme de current tuple ID). Cette colonne syst�me contient le num�ro de bloc du fichier et la position dans le bloc pour cette ligne. Connaissant la table, le CTID peut �tre utilis� pour r�cup�rer la ligne originale de t1 � mettre � jour. Apr�s avoir ajout� le CTID dans la liste cible, la requ�te ressemble � ceci
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
Maintenant, un autre d�tail de PostgreSQL entre en jeu. Les anciennes lignes de la table ne sont pas surcharg�es et cela explique pourquoi ROLLBACK est rapide. Avec un UPDATE, la nouvelle ligne r�sultat est ins�r�e dans la table (apr�s avoir enlev� le CTID) et, dans le nouvel en-t�te de ligne de l'ancienne ligne, vers o� pointe le CTID, les entr�es cmax et xmax sont configur�es par le compteur de commande actuel et par l'identifiant de transaction actuel. Du coup, l'ancienne ligne est cach�e et, apr�s validation de la transaction, le nettoyeur (vacuum) peut r�ellement la d�placer.
Connaissant tout ceci, nous pouvons simplement appliquer les r�gles de vues de la m�me fa�on que toute autre commande. Il n'y a pas de diff�rence.
L'exemple ci-dessus d�montre l'incorporation des d�finitions de vues par le syst�me de r�gles dans l'arbre de requ�te original. Dans le deuxi�me exemple, un simple SELECT d'une vue a cr�� un arbre de requ�te final qui est une jointure de quatre tables (unit a �t� utilis� deux fois avec des noms diff�rents).
Le b�n�fice de l'impl�mentation des vues avec le syst�me de r�gles est que le planificateur a toute l'information sur les tables � parcourir et sur les relations entre ces tables et les qualifications restrictives � partir des vues et les qualifications � partir de la requ�te originale dans un seule arbre de requ�te. Et c'est toujours la situation quand la requ�te originale est d�j� une jointure sur des vues. Le planificateur doit d�cider du meilleur chemin pour ex�cuter la requ�te et plus le planificateur a d'informations, meilleure sera la d�cision. Le syst�me de r�gles impl�ment� dans PostgreSQL s'en assure, c'est toute l'information disponible sur la requ�te � ce moment.
Qu'arrive-t'il si une vue est nomm�e comme la relation cible d'un INSERT, UPDATE ou DELETE ? Apr�s avoir r�alis�es les substitutions d�crites ci-dessus, nous aurons un arbre de requ�te dans lequel la relation r�sultante pointe vers une entr�e de la table d'�chelle de la sous-requ�te. Ceci ne fonctionnera pas car la r��criture renvoie une erreur si elle aper�oit qu'elle a produit une telle chose.
Pour modifier ceci, nous pouvons d�finir des r�gles modifiant le comportement de ce type de commandes. C'est donc le th�me de la prochaine section.
Pr�c�dent | Sommaire | Suivant |
Syst�me de r�gles | Niveau sup�rieur | R�gles sur INSERT, UPDATE et DELETE |