[GENERAL] Trigger before delete does fire before, but delete doesn't not happen
-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 EXISTSp_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 EXISTSp_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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
-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 EXISTSp_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 EXISTSp_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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
On Sunday 30 November 2008 11:18:12 pm Stéphane A. Schildknecht wrote: > 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 EXISTSp_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 EXISTSp_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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
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: > > > > > 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 EXISTSp_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 EXISTSp_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 > [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
Adrian Klaver <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
- "Tom Lane" <[EMAIL PROTECTED]> wrote: > Adrian Klaver <[EMAIL PROTECTED]> 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. > Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple for the row with id of 1. This means the original statement "delete from commande where id=1" runs against a version of the row that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and changed the row from under the original statement. > regards, tom lane Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
Adrian Klaver <[EMAIL PROTECTED]> writes: > Thanks for the explanation. Just so I am clear,the act of updating the row in > p_commandeligne_ad creates a new tuple for the row with id of 1. This means > the original statement "delete from commande where id=1" runs against a > version of the row that no longer exists and becomes a no-op statement. This > happens because the trigger was run as BEFORE and changed the row from under > the original statement. Right. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane a écrit : > Adrian Klaver <[EMAIL PROTECTED]> writes: >> Thanks for the explanation. Just so I am clear,the act of updating the row >> in p_commandeligne_ad creates a new tuple for the row with id of 1. This >> means the original statement "delete from commande where id=1" runs against >> a version of the row that no longer exists and becomes a no-op statement. >> This happens because the trigger was run as BEFORE and changed the row from >> under the original statement. > > Right. > > regards, tom lane Thanks for having helped me understand better why it couldn't be a logical way of acting. 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 iD8DBQFJNRmvA+REPKWGI0ERAkeIAKCMucAjbCS8tw5kXJqyCuNWS7pMjQCgu2MU U4rECUpyOm5rqnr0FRmBT6o= =b7ow -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
On Tuesday 02 December 2008 3:19:11 am Stéphane A. Schildknecht wrote: > Tom Lane a écrit : > > Adrian Klaver <[EMAIL PROTECTED]> writes: > >> Thanks for the explanation. Just so I am clear,the act of updating the > >> row in p_commandeligne_ad creates a new tuple for the row with id of 1. > >> This means the original statement "delete from commande where id=1" runs > >> against a version of the row that no longer exists and becomes a no-op > >> statement. This happens because the trigger was run as BEFORE and > >> changed the row from under the original statement. > > > > Right. > > > > regards, tom lane > > Thanks for having helped me understand better why it couldn't be a logical > way of acting. > > Best regards, Actually there is a logic to it once you realize that an UPDATE in Postgres is really a DELETE and INSERT operation. It is a concept that still catches me on a regular basis. To see what is going on substitute ctid for oid in your test case. This will show that the ctid(current tuple id) is changing for the row you are deleting in commande. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general