Avec PostgreSQL, les vues sont implémentées en
utilisant le système de règles. Une vue est de manière basique une table
vide (sans données stockées) avec une règle ON SELECT DO
INSTEAD
. Par convention, cette règle est nommée
_RETURN
.
Donc une vue comme
CREATE VIEW ma_vue AS SELECT * FROM ma_table;
est pratiquement identique à
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;
sauf que vous ne pouvez pas l'écrire ainsi car les tables ne sont pas
autorisées à avoir des règles ON SELECT
.
Une vue peut aussi avoir d'autres types de règles DO
INSTEAD
, autorisant l'exécution de commandes
INSERT
, UPDATE
ou
DELETE
sur la vue malgré qu'elle ne stocke
pas de données. Ceci est discuté plus bas, dans
Section 41.2.4.
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
à 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.
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, least(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.
Les deux entrées supplémentaires de la table d'échelle pour new
et old
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 unite VALUES ('cm', 1.0); INSERT INTO unite VALUES ('m', 100.0); INSERT INTO unite 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 transmis 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, least(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, least(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;
Ceci pourrait sembler inefficace mais le planificateur rassemblera ceci en un arbre de requête à un seul niveau en « remontant » les sous-requêtes, puis il planifiera les jointures comme si nous les avions écrites manuellement. Donc remonter l'arbre de requête est une optimisation dont le système de réécriture n'a pas à se soucier lui-même.
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, le type de commande n'est pas nécessaire pour les règles de la vue mais la relation résultante pourrait affecter la façon dont la requête sera réécrite car une attention particulière doit être prise si la relation résultante est une vue.
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 FROM t2 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 FROM t2 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;
Mais il existe un petit problème dans UPDATE
: la
partie du plan d'exécution qui fait la jointure ne prête pas attention à
l'intérêt des résultats de la jointure. Il produit un ensemble de lignes.
Le fait qu'il y a une commande SELECT
et une commande
UPDATE
est géré plus haut dans l'exécuteur où cette
partie sait qu'il s'agit d'une commande UPDATE
, et elle
sait que ce résultat va aller dans la table t1
. Mais
lesquels de ces lignes vont être remplacées par la nouvelle ligne ?
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 éventuellement la supprimer.
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
? Faire simplement les substitutions
décrites ci-dessus donnerait un arbre de requêtes dont le résultat
pointerait vers une entrée de la table en sous-requête. Cela ne
fonctionnera pas. Néanmoins, il existe différents moyens permettant à
PostgreSQL de supporter la mise à jour d'une
vue. Dans l'ordre de complexité pour l'expérience utilisateur :
substituer automatiquement la table sous-jacente par la vue, exécuter un
trigger utilisateur, ou réécrire la requête par une règle utilisateur.
Ces options sont discutées ci-dessous.
Si la sous-requête fait une sélection à partir d'une relation simple et
qu'elle est suffisamment simple, le processus de réécriture peut
automatiquement remplacé la sous-requête avec la relation sous-jacente
pour que l'INSERT
, l'UPDATE
ou
le DELETE
soit appliqué correctement sur la relation
de base. Les vues qui sont « suffisamment simples » pour cela
sont appelées des vues automatiquement modifiables.
Pour des informations détaillées sur ce type de vue, voir
CREATE VIEW.
Sinon, l'opération peut être gérée par un trigger INSTEAD
OF
, créé par l'utilisateur, sur la vue (see CREATE TRIGGER).
La réécriture fonctionne légèrement différemment dans ce cas. Pour
INSERT
, la réécriture ne fait rien du tout avec la vue,
la laissant comme relation résultante de la requête. Pour
UPDATE
et
DELETE
, il est toujours nécessaire d'étendre la requête
de la vue pour récupérer les « anciennes » lignes que la commande
va essayer de mettre à jour ou supprimer. Donc la vue est étendue comme
d'habitude mais une autre entrée de table non étendue est ajoutée à la
requête pour représenter la vue en tant que relation résultante.
Le problème qui survient maintenant est d'identifier les lignes à mettre à
jour dans la vue. Rappelez-vous que, quand la relation résultante est une
table, une entrée CTID spéciale est ajoutée à la liste
cible pour identifier les emplacements physiques des lignes à mettre à jour.
Ceci ne fonctionne pas si la relation résultante est une vue car une vue
n'a pas de CTID, car ses lignes n'ont pas d'emplacements
physiques réels. À la place, pour une opération UPDATE
ou DELETE
, une entrée wholerow
(ligne
complète) spéciale est ajoutée à la liste cible, qui s'étend pour inclure
toutes les colonnes d'une vue. L'exécuteur utilise cette valeur pour
fournir l'« ancienne » ligne au trigger INSTEAD
OF
. C'est au trigger de savoir ce que la mise à jour est supposée
faire sur les valeurs des anciennes et nouvelles lignes.
Une autre possibilité est que l'utilisateur définisse des vues
INSTEAD
qui indiquent les actions à substituer pour les
commandes INSERT
, UPDATE
et
DELETE
sur une vue. Ces règles vont réécrire la commande,
typiquement en une commande qui met à jour une ou plusieurs tables, plutôt
que des vues. C'est le thème de Section 41.4.
Notez que les règles sont évaluées en premier, réécrivant la requête
originale avant qu'elle ne soit optimisée et exécutée. Du coup, si une vue
a des triggers INSTEAD OF
en plus de règles sur
INSERT
, UPDATE
ou
DELETE
, alors les règles seront évaluées en premier et,
suivant le résultat, les triggers pourraient être utilisés.
La réécriture automatique d'une requête INSERT
,
UPDATE
ou DELETE
sur une vue simple
est toujours essayée en dernier. Du coup, si une vue a des règles ou des
triggers, ces derniers surchargeront le comportement par défaut des vues
automatiquement modifiables.
S'il n'y a pas de règles INSTEAD
ou de triggers
INSTEAD OF
sur la vue et que le processus de
réécriture ne peut pas réécrire automatiquement la requête sous la forme
d'une mise à jour de la relation sous-jacente, une erreur sera renvoyée
car l'exécuteur ne peut pas modifier une vue.