Forums PostgreSQL.fr

bebert73

Membre

foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Bonjour,

Quelle est la façon la plus élégante pour restreindre le champ d'une foreign key (c'est à dire faire une foreign key sur une vue, si c'était possible) ?

Je vois deux solutions :
- soit faire la foreign key sur la table, puis gérer les restrictions via un trigger on insert/update
- soit passer par des vues matérialisées

Concernant la 2ème options, les vues matérialisées ne sont pas gérées dans PostgreSQL. J'ai trouvé sur le net la méthode de Jonathan Gardner pour gérer les vues matérialisées, mais ça date de 2004. C'est toujours encore ce qu'il y a de mieux ? (http://www.benjaminarai.com/benjamin_ar … _views.php)

Plus globalement, quelle technique employez-vous quand vous souhaité sécuriser un peu mieux l'intégrité de vos données en restreignant le champ d'application d'une clé étrangère ?

Marc Cousin

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Sur quoi voulez vous restreindre «le champ d'une foreign key» ?

Pour ce qui est des vues matérialisées, c'est toujours aussi manuel sous Postgres pour le moment.

---

Marc.

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

supposons le cas suivant (pour être plus clair sur ce que je cherche à faire, je fais "comme si" on pouvait faire une foreign key sur une vue)

create table toto (
   id        integer primary_key
   nom    varchar,
   valide  boolean default false;
);

create view vtoto as select * from toto where valide = true;

create table titi (
   name     varchar primary key,
   toto_id   integer references vtoto(id);
);

la syntaxe est bien sur incorrecte vu qu'on ne peut pas faire de foreign key sur une vue, ma question est simplement : comment gérez-vous ce genre de situation (à savoir que dans le champ toto_id de la table titi je ne veux autoriser que des "id" de la table toto pour lesquels valide est TRUE)

à priori le plus simple serait le trigger, mais je me demandais si les vues matérialisées étaient indiquées dans ce cas, ou s'il y a une autre solution à laquelle je ne pense pas ?

)

Marc Cousin

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Pas la peine de faire un trigger pour si peu: vous pouvez le faire simplement avec une contrainte check qui appelle une fonction. Évidemment, vous n'aurez pas les options 'CASCADE' par exemple.

Quelque chose comme CHECK ( verif_valide(toto_id))
Et définir la fonction verif_valide comme suit (non testé, mais c'est pour vous donner une idée de la chose):
CREATE FUNCTION verif_valide(int) RETURNS BOOLEAN LANGUAGE SQL AS $$
  SELECT EXISTS (SELECT 1 FROM toto WHERE VALIDE=true AND id=$1;
$$;

---

Marc.

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

ah bah oui en effet, tout simplement !

comme quoi j'ai bien fait de demander :-)

merci !

Marc Cousin

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Tiens, j'ai oublié la parenthèse fermante de mon EXISTS

---

Marc.

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

je vous pardonne...

SQLpro

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Pas la peine de faire un trigger pour si peu: vous pouvez le faire simplement avec une contrainte check qui appelle une fonction. Évidemment, vous n'aurez pas les options 'CASCADE' par exemple.

Quelque chose comme CHECK ( verif_valide(toto_id))
Et définir la fonction verif_valide comme suit (non testé, mais c'est pour vous donner une idée de la chose):
CREATE FUNCTION verif_valide(int) RETURNS BOOLEAN LANGUAGE SQL AS $$
  SELECT EXISTS (SELECT 1 FROM toto WHERE VALIDE=true AND id=$1;
$$;

Non, ta solution est fausse. Car que fais t-on si valide passe de true à false ?

En fait il n'existe aucun solution simple car il s'agit d'une contrainte dynamique. Ce serait possible si PG implémentait les assertions. Dans ce cas, l'ordre SQL serait :

CREATE ASSERTION A_TOTO_TOTI
AS
CHECK NOT EXIST(SELECT *
                FROM   titi
                WHERE  toto_id IN (SELECT id 
                                   FROM   toto
                                   WHERE  valide IS TRUE));

Malheureusement ce n'est pas possible et rare sont les SGBDR à la faire car cela pose des problèmes de performance...

La seule solution reste donc aux trggers et il en faut deux :
1 sur INSERT + UPDATE sur la table TITI
1 sur INSERT + UPDATE sur la table TOTO

A +

Dernière modification par SQLpro (29/05/2011 10:09:47)

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

En effet, la solution de Marc Cousin fonctionne bien lors de l'insertion et update dans titi, par contre elle ne gère pas le cas ou la valeur de valide dans toto change. Si valide passe a false, titi possèdera un champ toto_id referençant un enregistrement qui sera passé à false.

je pense que c'est ce que Marc voulait dire dans son message quand il disait "vous n'aurez pas les options CASCADE'" : l'intégrité référentielle n'est plus garantie

pour le garantir, il faut en effet un trigger sur TOTO

Mais à mon avis, on n'a pas besoin de trigger sur TITI, la contrainte CHECK suffit

c'est d'ailleurs la solution que j'ai retenue :
- contrainte CHECK dans TITI
- trigger sur TOTO pour empêcher de mettre valide à FALSE quand il y a des enregistrements dans TITI

Marc Cousin

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

C'est exactement ce que j'entendais par 'ça ne cascade pas': toute modification sur l'autre table n'est pas vérifiée par la contrainte check simple que j'avais donnée. J'aurais du détailler davantage.

On peut évidemment résoudre le problème avec un trigger sur toto. Ou une contrainte check… ce que je trouve plus lisible (j'ai tendance à chercher les contraintes d'intégrité dans les contraintes avant d'aller farfouiller dans les triggers), comme de mettre un nom de fonction bien explicite…

Le but de mon exemple était bien de simplement vous montrer qu'on pouvait mettre des fonctions dans le check, ce qui permettait de résoudre ce genre de problématique.

---

Marc.

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

on est d'accord

la solution finale que j'ai retenue est composée
- d'une foreign key
- d'une contrainte check
- et d'un trigger ON UPDATE sur la table toto, pour simuler le RESTRICT (on aurait bien sur aussi pu écrire le trigger différemment si on voulait simuler le CASCADE)

je pense en effet que c'est plus efficace que de mettre deux triggers.

ma solution finale

create table toto (
   id        integer primary_key
   nom    varchar,
   valide  boolean default false;
);

create table titi (
   name     varchar primary key,
   toto_id   integer references toto(id)
   constraint toto_id_check check (verif_valide(toto_id))
);

et le trigger (sur la table toto) :

CREATE OR REPLACE FUNCTION toto_verif_id_utilise_ou_pas() RETURNS TRIGGER AS $$
DECLARE
    compteur    INTEGER := 0;
BEGIN
    IF ((OLD.valide = TRUE) AND (NEW.valide = FALSE)) THEN
        SELECT         COUNT(*) INTO compteur
            FROM    titi
            WHERE    toto_id = NEW.id;
    END IF;
    IF (compteur > 0) THEN
        RAISE EXCEPTION 'Vous ne pouvez pas mettre valide à FALSE car cet id est utilisé dans la table titi.';
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER toto_verif_id_utilise_ou_pas BEFORE UPDATE ON toto
    FOR EACH ROW EXECUTE PROCEDURE toto_verif_id_utilise_ou_pas();

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

euh...après réflexion, il y a encore plus simple, c'est d'écrire une deuxième fonction check (dans la table toto cette fois, appelons la count_totoid_dans_titi()). Cette fonction renvoie, pour un toto_id donné, le nombre de lignes présentes dans la table titi. Le check vérifie que si valide est mis à false, alors count_totoid_dans_titi doit renvoyer impérativement 0. Je suppose qu'il faudra mettre un CASE dans le check.

donc au finish ça ferait 2 contraintes CHECK, au lieu de 2 triggers

ça me semble en effet plus élégant

je vais m'y coller...

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

yep, ça semble marcher avec deux CHECK
donc ci-dessous ma solution finale (on espère)

en terme de code à  écrire, les deux solutions me semblent équivalentes...mais ça semble en effet plus élégant avec deux CHECK qu'avec deux TRIGGERS

create table toto (
   id        integer primary_key
   nom    varchar,
   valide  boolean default false;
);

create table titi (
   name     varchar primary key,
   toto_id   integer references toto(id)
   constraint toto_id_check check (verif_valide(toto_id))
);

CREATE FUNCTION count_toto_id_dans_titi (IN id INTEGER) RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT name
                    FROM    titi
                    WHERE    toto_id = $1);                       
$$ LANGUAGE SQL;

ALTER TABLE toto
ADD CONSTRAINT check_toto_id_utilise_dans_titi
    CHECK (    CASE    WHEN valide = FALSE
                    THEN     count_toto_id_dans_titi(id) = FALSE
            END);

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

question quand même à Frédéric Brouard (dont le livre SQL 2ème édition m'a fait passer quelques nuits blanches )

pourquoi opteriez-vous pour 2 triggers plutôt que 2 checks ? des différences en terme de performances ?

Marc Cousin

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Il y a quand même un cas qui ne va pas marcher avec cette solution:
état initial: titi n'a pas d'enregistrements référençant toto.
T1: update toto et passe valide à false
T2: insère des enregistrements dans titi
T1: commit
T2: commit

On doit pouvoir s'en sortir en mettant un select for share sur l'enreg de toto dans verif_valide.

---

Marc.

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

exact, j'ai fait le test avec deux transactions concurrentes, le problème se pose en effet

et encore exact, en mettant FOR SHARE dans la fonction verif_valide ca regle le probleme, j'ai testé aussi

super!

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

tient, je viens de devenir membre silver, comment ça se fait ?

ça a droit à un porte-clé ou un pins, un membre silver ?

gleu

Administrateur

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Non, c'est juste par rapport au nombre de messages dans le forum

---

Guillaume.

Marc Cousin

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Ouais, on est trop sympas ici, on donne les badges super vite…

---

Marc.

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

pas de badge ? flute alors... 

sans vouloir être pinailleur, pour clôturer ce post j'aimerais quand même savoir : entre la solution avec 2 CHECKS et la solutions avec 2 TRIGGERS préconisée par Frédéric Brouard, y aurait-t-il des raisons objectives de préférer une solution par rapport à l'autre ? (à part l'aspect "c'est plus élégant", qui est vous en conviendrez tout à fait relatif)

Dernière modification par bebert73 (30/05/2011 09:16:15)

bebert73

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

je me réponds partiellement à moi-même : dans certains cas les triggers sont quand même préférables

voir l'exemple de mon autre post, relatif à la désactivation des contraintes check : dans le cas où on n'a pas deux tables, mais une seule table avec une contrainte check "circulaire" (ie d'une colonne sur une autre de la même table), un problème se pose lors de la restauration, car à priori on ne peut pas désactiver les contraintes check

on est donc obligé dans ce cas d'utiliser des triggers, qu'on peut désactiver lors d'une sauvegarde / restauration

SQLpro

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

je me réponds partiellement à moi-même : dans certains cas les triggers sont quand même préférables

voir l'exemple de mon autre post, relatif à la désactivation des contraintes check : dans le cas où on n'a pas deux tables, mais une seule table avec une contrainte check "circulaire" (ie d'une colonne sur une autre de la même table), un problème se pose lors de la restauration, car à priori on ne peut pas désactiver les contraintes check

on est donc obligé dans ce cas d'utiliser des triggers, qu'on peut désactiver lors d'une sauvegarde / restauration

J'ai fait des tests sur SQL Server (pas sur PG) pour un livre à paraître aux US, sur la comparaison des contraintes complexes entre trigger et check avec fonction. La fonctions gagne dans 95% des cas par rapport aux triggers.
Comme le moteur PG est, à la base, le même (ingres) je pense que les résultats seraient similaires avec PG.
Mais dans le doute, seul un benchmark serait le bienvenu !

A +

Dernière modification par SQLpro (31/05/2011 14:08:16)

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

flo

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Ah non, le moteur PG n'est pas ingres. C'est juste le même créateur, au départ.
Quelqu'un a déjà fait ce test sur PostgreSQL, sinon?

SQLpro

Membre

Re : foreign key sur une vue : par trigger, par vues matérialisées, ... ?

Ah non, le moteur PG n'est pas ingres. C'est juste le même créateur, au départ.
Quelqu'un a déjà fait ce test sur PostgreSQL, sinon?

Oui, enfin, ce sont les mêmes prinipes, algo et tutti quanti qui sont mis en jeu...
Lisez ce que raconte Stonebraker dans ce livre : http://www.amazon.com/Readings-Database … 0262693143

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *