Documentation PostgreSQL 8.0.25 | ||||
---|---|---|---|---|
Précédent | Arrière rapide | Chapitre 33. 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 à agir comme des 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 chaussure AS SELECT sh.nom_chaussure, sh.dispo_chaussure, sh.couleur_chaussure, sh.long_min_chaussure, sh.long_min_chaussure * un.facteur_unite AS long_min_chaussure_cm, sh.long_max_chaussure, sh.long_max_chaussure * un.facteur_unite AS long_max_chaussure_cm, sh.unite_long_chaussure FROM donnees_chaussure sh, unite un WHERE sh.unite_long_chaussure = un.nom_unite; CREATE VIEW lacet AS 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, unite u WHERE s.unite_lacet = u.nom_unite; CREATE VIEW chaussure_prete AS SELECT rsh.nom_chaussure, rsh.dispo_chaussure, rsl.nom_lacet, rsl.dispo_lacet, min(rsh.dispo, rsl.dispo_lacet) AS total_avail FROM chaussure rsh, lacet rsl WHERE rsl.couleur_lacet = rsh.couleur AND rsl.longueur_lacet_cm >= rsh.long_min_chaussure_cm AND rsl.longueur_lacet_cm <= rsh.long_max_chaussure_cm;
La commande CREATE VIEW pour la vue lacet (qui est la plus simple que nous avons) écrira une relation lacet 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 lacet est référencée dans une table de la requête. La règle n'a aucune qualification de règle (discuté plus tard, avec les règles autres que SELECT car les règles SELECT ne le sont 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 donnees_lacet 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 en magasin dont nous avons les lacets correspondants (couleur et longueur) et pour lesquels le nombre total de pairs correspondants exactement est supérieur ou égal à deux.
SELECT * FROM chaussure_prete WHERE total_avail >= 2; nom_chaussure | dispo | nom_lacet | dispo_lacet | 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 chaussure_prete.nom_chaussure, chaussure_prete.dispo, chaussure_prete.nom_lacet, chaussure_prete.dispo_lacet, chaussure_prete.total_avail FROM chaussure_prete chaussure_prete WHERE chaussure_prete.total_avail >= 2;
La première règle appliquée sera celle de la vue chaussure_prete et cela résultera en cet arbre de requête
SELECT chaussure_prete.nom_chaussure, chaussure_prete.dispo, chaussure_prete.nom_lacet, chaussure_prete.dispo_lacet, chaussure_prete.total_avail FROM (SELECT rsh.nom_chaussure, rsh.dispo, rsl.nom_lacet, rsl.dispo_lacet, min(rsh.dispo, rsl.dispo_lacet) AS total_avail FROM chaussure rsh, lacet rsl WHERE rsl.couleur_lacet = rsh.couleur AND rsl.longueur_lacet_cm >= rsh.long_min_chaussure_cm AND rsl.longueur_lacet_cm <= rsh.long_max_chaussure_cm) chaussure_prete WHERE chaussure_prete.total_avail >= 2;
De façon similaire, les règles pour chaussure et lacet sont substituées dans la table d'échelle de la sous-requête, amenant à l'arbre de requête final à trois niveaux :
SELECT chaussure_prete.nom_chaussure, chaussure_prete.dispo, chaussure_prete.nom_lacet, chaussure_prete.dispo_lacet, chaussure_prete.total_avail FROM (SELECT rsh.nom_chaussure, rsh.dispo, rsl.nom_lacet, rsl.dispo_lacet, min(rsh.dispo, rsl.dispo_lacet) AS total_avail FROM (SELECT sh.nom_chaussure, sh.dispo, sh.couleur, sh.long_min_chaussure, sh.long_min_chaussure * un.facteur_unite AS long_min_chaussure_cm, sh.long_max_chaussure, sh.long_max_chaussure * un.facteur_unite AS long_max_chaussure_cm, sh.unite_long_chaussure FROM donnees_chaussure sh, unit un WHERE sh.unite_long_chaussure = un.nom_unite) rsh, (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) rsl WHERE rsl.couleur_lacet = rsh.couleur AND rsl.longueur_lacet_cm >= rsh.long_min_chaussure_cm AND rsl.longueur_lacet_cm <= rsh.long_max_chaussure_cm) chaussure_prete WHERE chaussure_prete.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. Cela 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' un SELECT, 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 |