Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 22/09/2015 03:02:15
- alassanediakite
- Membre
violation de l'intégrité référentielle
Salut
La situation...
Les tables tm et tf (tf liée à tm par une intégrité référentielle)
create table tm(idm serial not null primary key, vm varchar);
create table tf(idf serial not null primary key, idm int not null references tm(idm) on delete cascade, vf varchar);Un fonction trigger qui annule la suppression dans tf
--le trigger
create function tgtf() returns trigger
as
$$
begin
return null;
end
$$ language plpgsql
--liaison à tf
create trigger tgf before delete on tf
FOR EACH ROW
EXECUTE PROCEDURE tgtf();insertion de données dans tm et tf
insert into tm(vm) values('a'),('b'),('c');
insert into tf(idm, vf) values(1, 'a1'),(1, 'a2'), (2, 'b1'), (2, 'b2'),(3, 'c1'),(3, 'c2');suppression d'une ligne de tm
delete from tm where idm=1;la jointure gauche
select tf.idm, tf.vf, tm.idm from tf left join tm on tf.idm=tm.idm;idm vf idm
1 a1 null
1 a2 null
2 b1 2
2 b2 2
3 c1 3
3 c2 3
Qu'en dites-vous?
J'avais une fois remarqué qu'une des mes bd présentait ce problème (violation d'intégrité référentielle) sans que je ne sache comment c'est arrivé. Maintenant que j'ai reproduit la situation, je veux comprendre le problème.
@+
Hors ligne
#2 22/09/2015 08:52:10
- gleu
- Administrateur
Re : violation de l'intégrité référentielle
Le problème que vous constatez correspond exactement à ce que vous demandez (ie, vous vous tirez vous-même une balle dans le pied).
Vous placez un trigger qui empêche les suppressions dans la table tf. Or, vous demandez avec la clause "ON DELETE CASCADE" de supprimer la ligne correspondante dans tf. Forcément, ça ne peut pas fonctionner 
Guillaume.
Hors ligne
#3 22/09/2015 15:34:37
- alassanediakite
- Membre
Re : violation de l'intégrité référentielle
Salut gleu
Vous devez plutôt considérer le fait de "violation d'intégrité" et non comment j'y suis arrivé (bien que cela aide à comprendre l'origine du PROBLEME).
Le trigger réel est plus étoffé que ça! C'est après réflexion que j'ai soupçonné le "RETURN NULL".
Quel que soit l'action de mon trigger, le SGBD ne doit accepter que les données soient incohérentes.
@+
Dernière modification par alassanediakite (22/09/2015 15:47:18)
Hors ligne
#4 22/09/2015 23:51:44
- gleu
- Administrateur
Re : violation de l'intégrité référentielle
OK. Posons le problème autrement. Vous avez la contrainte d'intégrité qui supprime et le trigger qui empêche la suppression. Que PostgreSQL doit-il choisir d'après vous ? accepter la violation de la contrainte d'intégrité ou ignorer l'exécution du trigger (vu que son action est en contradiction avec la contrainte d'intégrité).
Guillaume.
Hors ligne
#5 23/09/2015 02:50:14
- alassanediakite
- Membre
Re : violation de l'intégrité référentielle
Salut
OK. Posons le problème autrement. Vous avez la contrainte d'intégrité qui supprime et le trigger qui empêche la suppression. Que PostgreSQL doit-il choisir d'après vous ? accepter la violation de la contrainte d'intégrité ou ignorer l'exécution du trigger (vu que son action est en contradiction avec la contrainte d'intégrité).
Annuler toute la transaction, c'est à dire depuis la suppression sur tm. Le trigger en donnant "NULL" est censé avoir signaler qu'il ANNULE la suppression sur tf, alors ce signal doit arrêter toute la transaction. Le respect des contraintes d'intrégrités doit être ABSOLU dans un SGBD et toute action contraire doit être rejeté.
@+
Dernière modification par alassanediakite (23/09/2015 02:51:35)
Hors ligne
#6 23/09/2015 08:23:44
- gleu
- Administrateur
Re : violation de l'intégrité référentielle
Si vous voulez annuler la transaction, faite un RAISE EXCEPTION, pas un RETURN NULL.
Guillaume.
Hors ligne
#7 23/09/2015 09:51:44
- arthurr
- Membre
Re : violation de l'intégrité référentielle
Guillaume,
Je pense que le problème soulevé n'est pas un problème de trigger, mais de comportement du moteur.
Après avoir fait la manip, j'ai ça :
forum=# select * from tm;
idm | vm
-----+----
2 | b
3 | c
(2 rows)
forum=# select * from tf;
idf | idm | vf
-----+-----+----
1 | 1 | a1
2 | 1 | a2
3 | 2 | b1
4 | 2 | b2
5 | 3 | c1
6 | 3 | c2
(6 rows) la table tf référence un idm = 1 qui n'existe pas dans la table tm.
J'utilises PostgreSQL depuis de nombreuses années, et je suis également surpris par ce comportement.
Pour moi, PostgreSQL devrait hurler (très fort) à un moment ou un autre pour ne pas laisser les données dans un état inconsistant.
Dernière modification par arthurr (23/09/2015 10:12:39)
Hors ligne
#8 23/09/2015 11:04:46
- gleu
- Administrateur
Re : violation de l'intégrité référentielle
J'ai bien compris le problème. Je ne dis pas que le comportement est le meilleur qu'il soit. Cependant, il correspond à ce qui est documenté.
Le faire hurler dans ce genre de cas va avoir de sérieuses implications en terme de performance.
Tant qu'on en ait aux comportements étonnants, vous savez bien sûr que si vous désactivez les triggers d'une table, vous désactivez aussi les vérifications des triggers ? voir http://blog.guillaume.lelarge.info/inde … g%C3%A8res
Guillaume.
Hors ligne
#9 23/09/2015 11:13:41
- gleu
- Administrateur
Re : violation de l'intégrité référentielle
Autre point d'information : http://www.postgresql.org/message-id/CA … .gmail.com
Guillaume.
Hors ligne
#10 23/09/2015 11:17:37
- arthurr
- Membre
Re : violation de l'intégrité référentielle
merci pour ces compléments.
"Malgré dix ans d'utilisation de PostgreSQL, je ne savais pas ça. J'aurais défendu mordicus le contraire." -> idem !
Hors ligne
#11 23/09/2015 16:55:50
- alassanediakite
- Membre
Re : violation de l'intégrité référentielle
Salut
Le cas que vous citez dans http://blog.guillaume.lelarge.info/inde … g%C3%A8res fait une désactivation implicite et produira un comportement normale (c'est à dire respect de la cohérence).
C'est le "RETURN NULL" qui n'est pas conforme aux exigence ACID d'un SGBD puis qu'il permet de SAUTER UNE LIGNE de la transaction.
Je cite la documentation...
Il peut retourner un pointeur NULL pour sauter l'opération pour la ligne courante. Ceci donne comme instruction à l'exécuteur de ne pas exécuter l'opération niveau ligne qui a lancé le déclencheur (l'insertion, la modification ou la suppression d'une ligne particulière de la table).
J'ai bien compris le problème. Je ne dis pas que le comportement est le meilleur qu'il soit.
En somme, on peut dire que "PostgreSQL peut exécuter des transaction AID c'est à dire non Cohérentes".
@+
Hors ligne
Pages : 1