34.2. Vues et syst�me de r�gles

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.

34.2.1. Fonctionnement des r�gles SELECT

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.

34.2.2. R�gles de vue dans des instructions autres que SELECT

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 :

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.

34.2.3. Puissance des vues dans PostgreSQL

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.

34.2.4. Mise � jour d'une vue

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.