Обсуждение: Trigger before delete does fire before, but delete doesn't not happen
Trigger before delete does fire before, but delete doesn't not happen
От
"Stéphane A. Schildknecht"
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I'm faced with something like a comprehension problem.
The exemple may be oversimplified, but, it seems same problem happens with updates.
To simplify, I have triggers on 2 tables (commande and commandeligne).
When deleting from table commande, a trigger fires to delete corresponding
entries in table commandeligne.
When deleting from table commandeligne a trigger fires to update sum of command
(column montant) in table commande.
I'm conscious that an "on delete cascade" on table commande would be really
better, conceptually and logically, but I would like to understand why I don't
get deletion of my tuple in table commande when firing triggers.
The test case I use is as follows :
#####
drop table commande cascade;
drop table commandeligne;
CREATE TABLE commande
(
id integer NOT NULL,
montant real,
CONSTRAINT id PRIMARY KEY (id)
)with oids;
CREATE TABLE commandeligne
(
id_commande integer NOT NULL references commande (id)
- -- on delete cascade on update cascade
,
montant real,
id_produit integer NOT NULL,
CONSTRAINT clef PRIMARY KEY (id_commande, id_produit)
)with oids;
CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS
$BODY$
BEGIN
-- RAISE NOTICE 'Table commandeligne : suppression de la ligne %', OLD.id;
DELETE FROM commandeligne WHERE id_commande = OLD.id;
-- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
DROP TRIGGER IF EXISTS p_commande_bd ON commande;
CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE
PROCEDURE p_commande_bd();
CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS
$BODY$
BEGIN
-- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande;
UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande;
-- RAISE NOTICE 'Table commande : ligne % maj (%)', OLD.id_commande,
OLD.montant;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
DROP TRIGGER IF EXISTS p_commandeligne_ad ON commandeligne;
CREATE TRIGGER p_commandeligne_ad AFTER DELETE ON commandeligne FOR EACH ROW
EXECUTE PROCEDURE p_commandeligne_ad();
- -------- First step : Creating first command
insert into commande(id, montant) values(1,150);
insert into commandeligne(id_commande,id_produit, montant) values(1,1,100);
insert into commandeligne(id_commande,id_produit, montant) values(1,2,20);
insert into commandeligne(id_commande,id_produit, montant) values(1,3,30);
select oid,* from commande where id=1;
select oid,* from commandeligne where id_commande=1;
- -------- 2nd step : Deletion of command 1
delete from commande where id=1;
select oid,* from commande where id=1;
select oid,* from commandeligne where id_commande=1;;
####
Command 1 is still there.
Thanks in advance.
Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFJL9o+A+REPKWGI0ERAv0gAJ0XU41ZkrjTzm8AL5aG+NtO3m6IOACgsY08
JsTE7QefA+yh87P7V/Lel10=
=3WLn
-----END PGP SIGNATURE-----
On Friday 28 November 2008 3:47:10 am Stéphane A. Schildknecht wrote: > drop table commande cascade; > drop table commandeligne; > > CREATE TABLE commande > ( > id integer NOT NULL, > montant real, > CONSTRAINT id PRIMARY KEY (id) > )with oids; > > CREATE TABLE commandeligne > ( > id_commande integer NOT NULL references commande (id) > -- on delete cascade on update cascade > , > montant real, > id_produit integer NOT NULL, > CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) > )with oids; > > CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS > $BODY$ > BEGIN > -- RAISE NOTICE 'Table commandeligne : suppression de la ligne %', > OLD.id; DELETE FROM commandeligne WHERE id_commande = OLD.id; > -- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id; > RETURN OLD; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > DROP TRIGGER IF EXISTS p_commande_bd ON commande; > CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE > PROCEDURE p_commande_bd(); > > CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS > $BODY$ > BEGIN > -- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande; > UPDATE commande SET montant=montant-OLD.montant WHERE id = > OLD.id_commande; -- RAISE NOTICE 'Table commande : ligne % maj (%)', > OLD.id_commande, OLD.montant; > RETURN NEW; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > DROP TRIGGER IF EXISTS p_commandeligne_ad ON commandeligne; > CREATE TRIGGER p_commandeligne_ad AFTER DELETE ON commandeligne FOR EACH > ROW EXECUTE PROCEDURE p_commandeligne_ad(); > > > -------- First step : Creating first command > insert into commande(id, montant) values(1,150); > insert into commandeligne(id_commande,id_produit, montant) values(1,1,100); > insert into commandeligne(id_commande,id_produit, montant) values(1,2,20); > insert into commandeligne(id_commande,id_produit, montant) values(1,3,30); > > select oid,* from commande where id=1; > select oid,* from commandeligne where id_commande=1; > > -------- 2nd step : Deletion of command 1 > delete from commande where id=1; When I run this test case I get: test=# -------- 2nd step : Deletion of command 1 test=# delete from commande where id=1; ERROR: update or delete on table "commande" violates foreign key constraint "commandeligne_id_commande_fkey" on table "commandeligne" DETAIL: Key (id)=(1) is still referenced from table "commandeligne". The FK in commandeligne (id_commande integer NOT NULL references commande (id)) is preventing the trigger from completing. > > select oid,* from commande where id=1; > select oid,* from commandeligne where id_commande=1;; -- Adrian Klaver aklaver@comcast.net
Re: Trigger before delete does fire before, but delete doesn't not happen
От
"Stéphane A. Schildknecht"
Дата:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Adrian Klaver a écrit : > On Friday 28 November 2008 3:47:10 am Stéphane A. Schildknecht wrote: >> drop table commande cascade; >> drop table commandeligne; >> >> CREATE TABLE commande >> ( >> id integer NOT NULL, >> montant real, >> CONSTRAINT id PRIMARY KEY (id) >> )with oids; >> >> CREATE TABLE commandeligne >> ( >> id_commande integer NOT NULL references commande (id) >> -- on delete cascade on update cascade >> , >> montant real, >> id_produit integer NOT NULL, >> CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) >> )with oids; >> >> CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS >> $BODY$ >> BEGIN >> -- RAISE NOTICE 'Table commandeligne : suppression de la ligne %', >> OLD.id; DELETE FROM commandeligne WHERE id_commande = OLD.id; >> -- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id; >> RETURN OLD; >> END; >> $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> >> DROP TRIGGER IF EXISTS p_commande_bd ON commande; >> CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE >> PROCEDURE p_commande_bd(); >> >> CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS >> $BODY$ >> BEGIN >> -- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande; >> UPDATE commande SET montant=montant-OLD.montant WHERE id = >> OLD.id_commande; -- RAISE NOTICE 'Table commande : ligne % maj (%)', >> OLD.id_commande, OLD.montant; >> RETURN NEW; >> END; >> $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> DROP TRIGGER IF EXISTS p_commandeligne_ad ON commandeligne; >> CREATE TRIGGER p_commandeligne_ad AFTER DELETE ON commandeligne FOR EACH >> ROW EXECUTE PROCEDURE p_commandeligne_ad(); >> >> >> -------- First step : Creating first command >> insert into commande(id, montant) values(1,150); >> insert into commandeligne(id_commande,id_produit, montant) values(1,1,100); >> insert into commandeligne(id_commande,id_produit, montant) values(1,2,20); >> insert into commandeligne(id_commande,id_produit, montant) values(1,3,30); >> >> select oid,* from commande where id=1; >> select oid,* from commandeligne where id_commande=1; >> >> -------- 2nd step : Deletion of command 1 >> delete from commande where id=1; > > When I run this test case I get: > > test=# -------- 2nd step : Deletion of command 1 > test=# delete from commande where id=1; > ERROR: update or delete on table "commande" violates foreign key > constraint "commandeligne_id_commande_fkey" on table "commandeligne" > DETAIL: Key (id)=(1) is still referenced from table "commandeligne". > > The FK in commandeligne (id_commande integer NOT NULL references commande > (id)) is preventing the trigger from completing. > Here, I don't get that error. Maybe you could try creating the commandeligne table like that : CREATE TABLE commandeligne ( id_commande integer NOT NULL -- references commande (id) -- on delete cascade on update cascade , montant real, id_produit integer NOT NULL, CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) )with oids; I'm running PG 8.3.5 or 8.2.11, result is the same. Regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJM4+zA+REPKWGI0ERAmeCAKCV5upN9r7174fzIQRLE6pajSc1tACg4pw0 SRmXwnN3huC4A6vteOo9CkE= =mlSt -----END PGP SIGNATURE-----
On Sunday 30 November 2008 11:18:12 pm Stéphane A. Schildknecht wrote: <Snip> > Adrian Klaver a écrit : > > > > > > When I run this test case I get: > > > > test=# -------- 2nd step : Deletion of command 1 > > test=# delete from commande where id=1; > > ERROR: update or delete on table "commande" violates foreign key > > constraint "commandeligne_id_commande_fkey" on table "commandeligne" > > DETAIL: Key (id)=(1) is still referenced from table "commandeligne". > > > > The FK in commandeligne (id_commande integer NOT NULL references > > commande (id)) is preventing the trigger from completing. > > Here, I don't get that error. > > Maybe you could try creating the commandeligne table like that : > > CREATE TABLE commandeligne > ( > id_commande integer NOT NULL > -- references commande (id) > -- on delete cascade on update cascade > , > montant real, > id_produit integer NOT NULL, > CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) > )with oids; > > I'm running PG 8.3.5 or 8.2.11, result is the same. > > Regards, It works if you change this to an AFTER DELETE trigger: DROP TRIGGER IF EXISTS p_commande_bd ON commande; CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); Use this version DROP TRIGGER IF EXISTS p_commande_bd ON commande; CREATE TRIGGER p_commande_bd after DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); The problem as far as I can tell is tuple visibility. By using a BEFORE trigger for the first function the OLD.* values are still available when the second trigger fires so UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande; has values to update in the commande table. For further clarifciation see: http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html In particular: The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level before trigger, because it hasn't happened yet. -- Adrian Klaver aklaver@comcast.net
On Monday 01 December 2008 7:27:48 am Adrian Klaver wrote: > On Sunday 30 November 2008 11:18:12 pm Stéphane A. Schildknecht wrote: > > <Snip> > > > Adrian Klaver a écrit : > > > When I run this test case I get: > > > > > > test=# -------- 2nd step : Deletion of command 1 > > > test=# delete from commande where id=1; > > > ERROR: update or delete on table "commande" violates foreign key > > > constraint "commandeligne_id_commande_fkey" on table "commandeligne" > > > DETAIL: Key (id)=(1) is still referenced from table "commandeligne". > > > > > > The FK in commandeligne (id_commande integer NOT NULL references > > > commande (id)) is preventing the trigger from completing. > > > > Here, I don't get that error. > > > > Maybe you could try creating the commandeligne table like that : > > > > CREATE TABLE commandeligne > > ( > > id_commande integer NOT NULL > > -- references commande (id) > > -- on delete cascade on update cascade > > , > > montant real, > > id_produit integer NOT NULL, > > CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) > > )with oids; > > > > I'm running PG 8.3.5 or 8.2.11, result is the same. > > > > Regards, > > It works if you change this to an AFTER DELETE trigger: > > DROP TRIGGER IF EXISTS p_commande_bd ON commande; > CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE > PROCEDURE p_commande_bd(); > > Use this version > > DROP TRIGGER IF EXISTS p_commande_bd ON commande; > CREATE TRIGGER p_commande_bd after DELETE ON commande FOR Each row EXECUTE > PROCEDURE p_commande_bd(); > > > The problem as far as I can tell is tuple visibility. By using a BEFORE > trigger for the first function the OLD.* values are still available when > the second trigger fires so > UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande; > has values to update in the commande table. > > For further clarifciation see: > http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html > > In particular: > The data change (insertion, update, or deletion) causing the trigger to > fire is naturally not visible to SQL commands executed in a row-level > before trigger, because it hasn't happened yet. Sorry, this applies to a trigger calling the function on the same table. > -- > Adrian Klaver > aklaver@comcast.net -- Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes:
> The problem as far as I can tell is tuple visibility.
Sort of: the triggers on commandeligne fire (and update the commande row)
at completion of the DELETE command inside p_commande_bd. This means
that by the time control returns from that trigger, the tuple version
that was targeted for deletion is already dead, so there's nothing to
do. It doesn't chain up to the newer version of the row.
An AFTER trigger would be better for this on general principles, anyway.
The rule of thumb is "use a BEFORE trigger to adjust what happens to the
target row, but use an AFTER trigger to propagate the changes to other
rows". If you don't do it that way then you have problems whenever
there are multiple triggers, since no individual BEFORE trigger can be
sure it knows the final state of the row.
regards, tom lane