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
aklaver@comcast.net

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

----- "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

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.

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
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> 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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane a ï¿œcrit :

Adrian Klaver <aklaver@comcast.net> 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-----

On Tuesday 02 December 2008 3:19:11 am Stéphane A. Schildknecht wrote:

Tom Lane a écrit :

Adrian Klaver <aklaver@comcast.net> 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
aklaver@comcast.net