Re: [BUGS] BUG #13632: violation de l'intégrité référentielle

On Wed, Sep 23, 2015 at 1:44 AM,  <allhassane@yahoo.fr> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      13632
> Logged by:          Alassane Diakité
> Email address:      allhassane@yahoo.fr
> PostgreSQL version: 9.4.4
> Operating system:   windows
> Description:
>
> Salut
> La situation...
> Les tables tm et tf (tf liée à tm par une intégrité référentielle)
> [code]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);[/code]
> Un fonction trigger qui annule la suppression dans tf
> [code]
> --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();
> [/code]
> insertion de données dans tm et tf
> [code]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');[/code]
> suppression d'une ligne de tm
> [code]delete from tm where idm=1;[/code]
> la jointure gauche
> [code]select tf.idm, tf.vf, tm.idm from tf left join tm on
> tf.idm=tm.idm;[/code]
> 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?

It's not great, but I don't think this is considered to be a bug: by
my reading of the archives[1], the fact that RI triggers' effects are
treated like anything else is considered valuable, even though it is
well known that you can break RI this way if you try.  Perhaps you
could argue that we should check if there is a remaining FK violation
after RI_FKey_cascade_del runs, or somehow detect that its DELETE had
been suppressed and only run an extra check in that case (that sounds
either expensive or complicated).

> 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.

The basic problem is that the RI triggers do cascading deletes by
executing regular DELETE statements whose effects are in turn subject
to interception by triggers, and you nullified the DELETE on tf.

If you want to let cascading deletes work but do something special
instead when users run explicit DELETE statements, perhaps you could
take advantage of the fact that RI triggers always run their
statements as the owner of the table: if you can arrange for explicit
DELETE statements to be run by a different user, maybe you could
detect that case and do something different only then.

[1] http://www.postgresql.org/message-id/10307.1372436251@sss.pgh.pa.us

--
Thomas Munro
http://www.enterprisedb.com