35.3. Règles sur insert, update et delete

Une façon simple de protéger les vues d'une exécution d' insert , d' update ou de delete sur elles est de laisser s'abandonner ces arbres de requête. Donc, nous pourrions créer les règles

CREATE RULE chaussure_ins_protect AS ON INSERT TO chaussure
    DO INSTEAD NOTHING;
CREATE RULE chaussure_upd_protect AS ON UPDATE TO chaussure
    DO INSTEAD NOTHING;
CREATE RULE chaussure_del_protect AS ON DELETE TO chaussure
    DO INSTEAD NOTHING;

Maintenant, si quelqu'un essaie de faire une de ces opérations sur la vue chaussure, le système de règles appliquera ces règles. Comme les règles n'ont pas d'action et sont de type instead, la liste résultante des arbres de requêtes sera vide et la requête entière deviendra vide car il ne reste rien à optimiser ou exécuter après que le système de règles en ait terminé avec elle.

Une façon plus sophistiquée d'utiliser le système de règles est de créer les règles qui réécrivent l'arbre de requête en un arbre faisant la bonne opération sur les vraies tables. Pour réaliser cela sur la vue lacet, nous créons les règles suivantes :

CREATE RULE lacet_ins AS ON INSERT TO lacet
    DO INSTEAD
    INSERT INTO donnees_lacet VALUES (
           NEW.nom_lacet,
           NEW.dispo_lacet,
           NEW.couleur_lacet,
           NEW.longueur_lacet,
           NEW.unite_lacet
    );

CREATE RULE lacet_upd AS ON UPDATE TO lacet
    DO INSTEAD
    UPDATE donnees_lacet
       SET nom_lacet = NEW.nom_lacet,
           dispo_lacet = NEW.dispo_lacet,
           couleur_lacet = NEW.couleur_lacet,
           longueur_lacet = NEW.longueur_lacet,
           unite_lacet = NEW.unite_lacet
     WHERE nom_lacet = OLD.nom_lacet;

CREATE RULE lacet_del AS ON DELETE TO lacet
    DO INSTEAD
    DELETE FROM donnees_lacet
     WHERE nom_lacet = OLD.nom_lacet;

Si vous voulez supporter les requêtes RETURNING sur la vue, vous devrez faire en sorte que les règles incluent les clauses RETURNING qui calcule les lignes de la vue. Ceci est assez simple pour des vues sur une seule table mais cela devient rapidement complexe pour des vues de jointure comme lacet. Voici un exemple pour le cas d'un INSERT

CREATE RULE lacet_ins AS ON INSERT TO lacet
    DO INSTEAD
    INSERT INTO donnees_lacet VALUES (
           NEW.nom_lacet,
           NEW.dispo_lacet,
           NEW.couleur_lacet,
           NEW.longueur_lacet,
           NEW.unite_lacet
    )
    RETURNING
           donnees_lacet.*,
           (SELECT donnees_lacet.longueur_lacet * u.facteur_unite
            FROM unite u WHERE donnees_lacet.unite_lacet = u.nom_unite);

Notez que cette seule règle supporte à la fois les INSERT et les INSERT RETURNING sur la vue -- la clause RETURNING est tout simplement ignoré pour un INSERT .

Maintenant, supposons que, quelque fois, un paquet de lacets arrive au magasin avec une grosse liste. Mais vous ne voulez pas mettre à jour manuellement la vue lacet à chaque fois. à la place, nous configurons deux petites tables, une où vous pouvez insérer les éléments de la liste et une avec une astuce spéciale. Voici les commandes de création :

CREATE TABLE lacet_arrive (
    arr_name    text,
    arr_quant   integer
);

CREATE TABLE lacet_ok (
    ok_name     text,
    ok_quant    integer
);

CREATE RULE lacet_ok_ins AS ON INSERT TO lacet_ok
    DO INSTEAD
    UPDATE lacet
       SET dispo_lacet = dispo_lacet + NEW.ok_quant
     WHERE nom_lacet = NEW.ok_name;

Maintenant, vous pouvez remplir la table lacet_arrive avec les données de la liste :

SELECT * FROM lacet_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)

Jetez un oeil rapidement aux données actuelles :

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        |           6 | 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)

Maintenant, déplacez les lacets arrivés dans :

INSERT INTO lacet_ok SELECT * FROM lacet_arrive;

et vérifiez le résultat :

SELECT * FROM lacet ORDER BY nom_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        |           6 | brown         |             60 | cm          |                60
 sl4        |           8 | black         |             40 | inch        |             101.6
 sl3        |          10 | black         |             35 | inch        |              88.9
 sl8        |          21 | brown         |             40 | inch        |             101.6
 sl5        |           4 | brown         |              1 | m           |               100
 sl6        |          20 | brown         |            0.9 | m           |                90
(8 rows)

SELECT * FROM lacet_log;

 nom_lacet | dispo_lacet | log_who| log_when                        
-----------+-------------+--------+----------------------------------
 sl7       |           6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 sl3       |          10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl6       |          20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl8       |          21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)

C'est un long chemin du insert ... select à ces résultats. Et la description de la transformation de l'arbre de requêtes sera la dernière dans ce chapitre. Tout d'abord, voici la sortie de l'analyseur

INSERT INTO lacet_ok
SELECT lacet_arrive.arr_name, lacet_arrive.arr_quant
  FROM lacet_arrive lacet_arrive, lacet_ok lacet_ok;

Maintenant, la première règle lacet_ok_ins est appliquée et transforme ceci en

UPDATE lacet
   SET dispo_lacet = lacet.dispo_lacet + lacet_arrive.arr_quant
  FROM lacet_arrive lacet_arrive, lacet_ok lacet_ok,
       lacet_ok *OLD*, lacet_ok *NEW*,
       lacet lacet
 WHERE lacet.nom_lacet = lacet_arrive.arr_name;

et jette l' insert actuel sur lacet_ok. la requête réécrite est passée de nouveau au système de règles et la seconde règle appliquée lacet_upd produit

UPDATE donnees_lacet
   SET nom_lacet = lacet.nom_lacet,
       dispo_lacet = lacet.dispo_lacet + lacet_arrive.arr_quant,
       couleur_lacet = lacet.couleur_lacet,
       longueur_lacet = lacet.longueur_lacet,
       unite_lacet = lacet.unite_lacet
  FROM lacet_arrive lacet_arrive, lacet_ok lacet_ok,
       lacet_ok *OLD*, lacet_ok *NEW*,
       lacet lacet, lacet *OLD*,
       lacet *NEW*, donnees_lacet donnees_lacet
 WHERE lacet.nom_lacet = lacet_arrive.arr_name
   AND donnees_lacet.nom_lacet = lacet.nom_lacet;

De nouveau, il s'agit d'une règle instead et l'arbre de requête précédent est jeté. Notez que cette requête utilise toujours la vue lacet. mais le système de règles n'a pas fini cette étape, donc il continue et lui applique la règle _return. Nous obtenons

UPDATE donnees_lacet
   SET nom_lacet = s.nom_lacet,
       dispo_lacet = s.dispo_lacet + lacet_arrive.arr_quant,
       couleur_lacet = s.couleur_lacet,
       longueur_lacet = s.longueur_lacet,
       unite_lacet = s.unite_lacet
  FROM lacet_arrive lacet_arrive, lacet_ok lacet_ok,
       lacet_ok *OLD*, lacet_ok *NEW*,
       lacet lacet, lacet *OLD*,
       lacet *NEW*, donnees_lacet donnees_lacet,
       lacet *OLD*, lacet *NEW*,
       donnees_lacet s, unit u
 WHERE s.nom_lacet = lacet_arrive.arr_name
   AND donnees_lacet.nom_lacet = s.nom_lacet;

Enfin, la règle log_lacet est appliquée, produisant l'arbre de requête supplémentaire

INSERT INTO lacet_log
SELECT s.nom_lacet,
       s.dispo_lacet + lacet_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM lacet_arrive lacet_arrive, lacet_ok lacet_ok,
       lacet_ok *OLD*, lacet_ok *NEW*,
       lacet lacet, lacet *OLD*,
       lacet *NEW*, donnees_lacet donnees_lacet,
       lacet *OLD*, lacet *NEW*,
       donnees_lacet s, unit u,
       donnees_lacet *OLD*, donnees_lacet *NEW*
       lacet_log lacet_log
 WHERE s.nom_lacet = lacet_arrive.arr_name
   AND donnees_lacet.nom_lacet = s.nom_lacet
   AND (s.dispo_lacet + lacet_arrive.arr_quant) <> s.dispo_lacet;

une fois que le système de règles tombe en panne de règles et renvoie les arbres de requêtes générés.

Donc, nous finissons avec deux arbres de requêtes finaux qui sont équivalents aux instructions SQL

INSERT INTO lacet_log
SELECT s.nom_lacet,
       s.dispo_lacet + lacet_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM lacet_arrive lacet_arrive, donnees_lacet donnees_lacet,
       donnees_lacet s
 WHERE s.nom_lacet = lacet_arrive.arr_name
   AND donnees_lacet.nom_lacet = s.nom_lacet
   AND s.dispo_lacet + lacet_arrive.arr_quant <> s.dispo_lacet;
           
UPDATE donnees_lacet
   SET dispo_lacet = donnees_lacet.dispo_lacet + lacet_arrive.arr_quant
  FROM lacet_arrive lacet_arrive,
       donnees_lacet donnees_lacet,
       donnees_lacet s
 WHERE s.nom_lacet = lacet_arrive.nom_lacet
   AND donnees_lacet.nom_lacet = s.nom_lacet;

Le résultat est que la donnée provenant d'une relation insérée dans une autre, modifiée en mise à jour dans une troisième, modifiée en mise à jour dans une quatrième, cette dernière étant tracée dans une cinquième, se voit réduite à deux requêtes.

Il y a un petit détail assez horrible. En regardant les deux requêtes, nous nous apercevons que la relation donnees_lacet apparaît deux fois dans la table d'échelle où cela pourrait être réduit à une seule occurrence. Le planificateur ne gère pas ceci et, du coup, le plan d'exécution de la sortie du système de règles pour insert sera

Nested Loop
  ->  Merge Join
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on s
        ->  Seq Scan
              ->  Sort
                    ->  Seq Scan on lacet_arrive
  ->  Seq Scan on donnees_lacet

alors qu'omettre la table d'échelle supplémentaire résulterait en un

Merge Join
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on s
  ->  Seq Scan
        ->  Sort
              ->  Seq Scan on lacet_arrive

qui produit exactement les mêmes entrées dans la table des traces. Du coup, le système de règles a causé un parcours supplémentaire dans la table donnees_lacet qui n'est absolument pas nécessaire. et le même parcours redondant est fait une fois de plus dans l' update . mais ce fut réellement un travail difficile de rendre tout ceci possible.

Maintenant, nous faisons une démonstration finale du système de règles de PostgreSQL™ et de sa puissance. disons que nous ajoutons quelques lacets avec des couleurs extraordinaires à votre base de données :

INSERT INTO lacet VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO lacet VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);

Nous voulons créer une vue vérifiant les entrées lacet qui ne correspondent à aucune chaussure pour la couleur. Voici la vue

CREATE VIEW lacet_mismatch AS
    SELECT * FROM lacet WHERE NOT EXISTS
        (SELECT nom_chaussure FROM chaussure WHERE couleur = couleur_lacet);

Sa sortie est

SELECT * FROM lacet_mismatch;

 nom_lacet | dispo_lacet | couleur_lacet | longueur_lacet | unite_lacet | longueur_lacet_cm
-----------+-------------+---------------+----------------+-------------+-------------------
 sl9       |           0 | pink          |             35 | inch        |              88.9
 sl10      |        1000 | magenta       |             40 | inch        |             101.6

Maintenant, nous voulons la configurer pour que les lacets qui ne correspondent pas et qui ne sont pas en stock soient supprimés de la base de données. Pour rendre la chose plus difficile à PostgreSQL™, nous ne les supprimons pas directement. À la place, nous créons une vue supplémentaire

CREATE VIEW lacet_can_delete AS
    SELECT * FROM lacet_mismatch WHERE dispo_lacet = 0;

et le faisons de cette façon :

DELETE FROM lacet WHERE EXISTS
    (SELECT * FROM lacet_can_delete
             WHERE nom_lacet = lacet.nom_lacet);

voilà :

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       |           6 | brown         |             60 | cm          |                60
 sl4       |           8 | black         |             40 | inch        |             101.6
 sl3       |          10 | black         |             35 | inch        |              88.9
 sl8       |          21 | brown         |             40 | inch        |             101.6
 sl10      |        1000 | magenta       |             40 | inch        |             101.6
 sl5       |           4 | brown         |              1 | m           |               100
 sl6       |          20 | brown         |            0.9 | m           |                90
(9 rows)

Un delete sur une vue, avec une qualification de sous-requête qui utilise au total quatre vues imbriquées/jointes, où l'une d'entre elles a une qualification de sous-requête contenant une vue et où les colonnes des vues calculées sont utilisées, est réécrite en un seul arbre de requête qui supprime les données demandées sur la vraie table.

Il existe probablement seulement quelques situations dans le vrai monde où une telle construction est nécessaire. Mais, vous vous sentez mieux quand cela fonctionne.