Обсуждение: FW: BUG in trigger and foreign keys

Following sample demonstrates incorrect behavior of the trigger.
The problem happens only when the table that has  the trigger also has foreign key constraints.
If no constrains is defined then the trigger works fine.
Detected on Postgres 7.3 RH - Linux.
Happens also in 7.3.1.

select  version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3

The problem can be reproduced by issuing the following SQL statement after creating the schema from the attached
script:
delete from reseller where reseller_id=1338;


The problematic output is :
try=# delete from reseller where reseller_id=1338;
NOTICE:  Gets here, ID 1338
NOTICE:  Gets here, l_val 1
NOTICE:  Gets here, r_val 22
NOTICE:  ...and executes stuff (decrementing with 22)
NOTICE:  Gets here, ID 1341
NOTICE:  Gets here, l_val 2
NOTICE:  Gets here, r_val 9
NOTICE:  ...and executes stuff (decrementing with 8)
NOTICE:  Gets here, ID 1342
NOTICE:  Gets here, l_val 10
NOTICE:  Gets here, r_val 17
NOTICE:  ...and executes stuff (decrementing with 8)
NOTICE:  Gets here, ID 1343
NOTICE:  Gets here, l_val 18
NOTICE:  Gets here, r_val 21
NOTICE:  ...and executes stuff (decrementing with 4)
NOTICE:  Gets here, ID 1350
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1351
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1352
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1353
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1354
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1355
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1356
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1357
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1358
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1359
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1363
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1368
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
DELETE 1

The correct output should be:
NOTICE:  Gets here, ID 1338
NOTICE:  Gets here, l_val 1
NOTICE:  Gets here, r_val 22
NOTICE:  ...and executes stuff (decrementing with 22)
NOTICE:  Gets here, ID 1341
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1342
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1343
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1350
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1351
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1352
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1353
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1354
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1355
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1356
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1357
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1358
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1359
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1363
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
NOTICE:  Gets here, ID 1368
NOTICE:  Gets here, l_val <NULL>
NOTICE:  Gets here, r_val <NULL>
DELETE 1

Attached sample code to recreate the problem:
CREATE SEQUENCE SeqTblIndex START 100;

CREATE TABLE reseller (
       reseller_id          int4 DEFAULT nextval('SeqTblIndex'),
       name                 varchar(64),
       parent_id            int4 DEFAULT 1,
       profile_id           int4,
       l_val                int4,
       r_val                int4
);

CREATE UNIQUE INDEX XPKreseller ON reseller
(
       reseller_id
);

CREATE UNIQUE INDEX XAK1reseller ON reseller
(
       name
);

CREATE OR REPLACE FUNCTION handle_tree_delete() RETURNS TRIGGER AS '
    DECLARE
        decr INTEGER;
    BEGIN
        RAISE NOTICE ''Gets here, ID %'', OLD.reseller_id;
        RAISE NOTICE ''Gets here, l_val %'', OLD.l_val;
        RAISE NOTICE ''Gets here, r_val %'', OLD.r_val;
        IF NOT OLD.l_val ISNULL THEN
            decr := (((OLD.r_val - OLD.l_val - 1) / 2 ) + 1) * 2;
            RAISE NOTICE ''...and executes stuff (decrementing with %)'', decr;

            UPDATE reseller SET parent_id = NULL, l_val = NULL, r_val = NULL WHERE l_val > OLD.l_val AND r_val <
OLD.r_val;
            DELETE FROM reseller WHERE parent_id ISNULL AND l_val ISNULL AND r_val ISNULL;

            UPDATE reseller SET l_val = l_val - decr WHERE l_val > OLD.l_val;
            UPDATE reseller SET r_val = r_val - decr WHERE r_val > OLD.r_val;
        END IF;
        RETURN OLD;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER on_reseller_delete_tree AFTER DELETE
    ON reseller
    FOR EACH ROW
    EXECUTE PROCEDURE handle_tree_delete();


/* additional tables */
CREATE TABLE profile_info (
       profile_id           int4 DEFAULT nextval('SeqTblIndex'),
       reseller_id          int4,
       profile_name         varchar(64)
);

CREATE UNIQUE INDEX XPKprofile_info ON profile_info
(
       profile_id
);


CREATE TABLE server_groups (
       server_group_id      int4 DEFAULT nextval('SeqTblIndex'),
       server_group_name    varchar(64),
       reseller_id          int4
);

CREATE TABLE sp_info (
       sp_id                int4 DEFAULT nextval('SeqTblIndex'),
       reseller_id          int4,
       name                 varchar(64)
);

CREATE TABLE vds (
       vds_id               int4 DEFAULT nextval('SeqTblIndex'),
       reseller_id          int4,
       name                 varchar(32)
);

CREATE TABLE reseller_links (
       reseller_id          int4,
       link_value           text
);

CREATE TABLE reseller_sched_reports (
       reseller_id          int4,
       report_name          varchar(64)
);

CREATE TABLE reseller_service_packages (
       reseller_id          int4,
       item_id              int4
);

CREATE TABLE reseller_plugins (
       reseller_id          int4,
       item_id              int4
);

CREATE TABLE reseller_server_groups (
       reseller_id          int4,
       item_id              int4
);


CREATE TABLE reseller_managed_servers (
       reseller_id          int4,
       item_id              int4
);



/* constraints */

ALTER TABLE VDS ADD CONSTRAINT FK_VDS1
FOREIGN KEY (reseller_id) REFERENCES Reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reseller_links ADD CONSTRAINT FK_reseller_links11
FOREIGN KEY (reseller_id) REFERENCES Reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reseller_sched_reports ADD CONSTRAINT FK_reseller_sched_reports1
FOREIGN KEY (reseller_id) REFERENCES Reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE sp_info ADD CONSTRAINT FK_sp_info1
FOREIGN KEY (reseller_id) REFERENCES Reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reseller ADD CONSTRAINT FK_reseller1
FOREIGN KEY (profile_id) REFERENCES profile_info(profile_id)
ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE reseller_service_packages ADD CONSTRAINT FK_reseller_service_packages1
FOREIGN KEY (reseller_id) REFERENCES reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reseller_server_groups ADD CONSTRAINT FK_reseller_server_groups1
FOREIGN KEY (reseller_id) REFERENCES reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reseller_plugins ADD CONSTRAINT FK_reseller_plugins1
FOREIGN KEY (reseller_id) REFERENCES reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE reseller ADD CONSTRAINT FK_reseller_parent
FOREIGN KEY (parent_id) REFERENCES reseller(reseller_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE server_groups ADD CONSTRAINT FK_server_groups_reseller3
FOREIGN KEY (reseller_id) REFERENCES reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reseller_managed_servers ADD CONSTRAINT FK_reseller_managed_servers1
FOREIGN KEY (reseller_id) REFERENCES reseller(reseller_id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;


/* data */


INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES (NULL, 1,'admin',0,67);

INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1,1338,'l1_1',1,22);


INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1,1339,'l1_2',23,44);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1,1340,'l1_3',45,66);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1338,1341,'l2_1',2,9);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1338,1342,'l2_2',10,17);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1338,1343,'l2_3',18,21);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1339,1344,'l2_4',24,31);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1339,1345,'l2_5',32,39);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1339,1346,'l2_6',40,43);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1340,1347,'l2_7',46,53);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1340,1348,'l2_8',54,61);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1340,1349,'l2_9',62,65);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1341,1350,'l3_1',3,4);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1341,1351,'l3_2',5,6);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1341,1352,'l3_3',7,8);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1342,1353,'l3_4',11,12);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1342,1354,'l3_5',13,14);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1342,1355,'l3_6',15,16);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1343,1356,'l3_7',19,20);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1344,1357,'l3_10',25,26);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1344,1358,'l3_11',27,28);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1344,1359,'l3_12',29,30);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1345,1360,'l3_13',33,34);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1345,1361,'l3_14',35,36);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1345,1362,'l3_15',37,38);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1346,1363,'l3_16',41,42);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1347,1364,'l3_19',47,48);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1347,1365,'l3_20',49,50);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1347,1366,'l3_21',51,52);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1348,1367,'l3_22',55,56);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1348,1368,'l3_23',57,58);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1348,1369,'l3_24',59,60);
INSERT INTO reseller(parent_id,reseller_id,name,l_val,r_val)
VALUES(1349,1370,'l3_25',63,64);


/*
delete from reseller where reseller_id=1338;
*/

"Jefim Matskin" <JefimM@sphera.com> writes:
> Following sample demonstrates incorrect behavior of the trigger.
> The problem happens only when the table that has  the trigger also has foreign key constraints.

What makes you think this is a bug?  It looks to me like you simply
haven't thought about the interaction between your AFTER DELETE trigger
and the triggers that implement the foreign-key behavior (in particular,
the ON UPDATE CASCADE and ON DELETE CASCADE clauses).

            regards, tom lane

If try it with the same  script - but without the constraints - you will see the difference.
And there should not be any since the data is the same in the tables.
This causes the tree structure to be updated incorrectly ( the reseller table is a tree and the delete trigger should
updatethe tree structure). 
You should also note that all the trigger invocations other  that the first one are caused by the delete that is issued
inthe first invocation of the trigger. 
But before that the l_val and r_val are set to NULL for the records to be deleted. So the trigger should print out
NULL'sand not the old values that are valid only when trigger is in his first invocation. 

The tree article is :
http://archives.postgresql.org/pgsql-sql/2002-05/msg00039.php

    regards, jefim.

> Following sample demonstrates incorrect behavior of the trigger.
> The problem happens only when the table that has  the trigger also has foreign key constraints.

What makes you think this is a bug?  It looks to me like you simply
haven't thought about the interaction between your AFTER DELETE trigger
and the triggers that implement the foreign-key behavior (in particular,
the ON UPDATE CASCADE and ON DELETE CASCADE clauses).

            regards, tom lane

"Jefim Matskin" <JefimM@sphera.com> writes:
> If try it with the same  script - but without the constraints - you will see the difference.
> And there should not be any since the data is the same in the tables.

No, it's not the same.  Consider the implications of the constraint
you added:

ALTER TABLE reseller ADD CONSTRAINT FK_reseller_parent
FOREIGN KEY (parent_id) REFERENCES reseller(reseller_id)
ON DELETE CASCADE ON UPDATE CASCADE;
^^^^^^^^^^^^^^^^^

This will cause the delete of reseller_id 1338 to propagate to the rows
in which 1338 appears as parent_id.  Which sure looks to me like it's
the behavior you're complaining of.

            regards, tom lane

Thank you for pointing that out. It seems that "oops I did it again".

What are the rules for the order of the invocation  of triggers defined for some table?
If several triggers are defined which one if executed first?

Jefim

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wed 25 December 2002 6:08
To: Jefim Matskin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] FW: BUG in trigger and foreign keys


"Jefim Matskin" <JefimM@sphera.com> writes:
> If try it with the same  script - but without the constraints - you will see the difference.
> And there should not be any since the data is the same in the tables.

No, it's not the same.  Consider the implications of the constraint
you added:

ALTER TABLE reseller ADD CONSTRAINT FK_reseller_parent
FOREIGN KEY (parent_id) REFERENCES reseller(reseller_id)
ON DELETE CASCADE ON UPDATE CASCADE;
^^^^^^^^^^^^^^^^^

This will cause the delete of reseller_id 1338 to propagate to the rows
in which 1338 appears as parent_id.  Which sure looks to me like it's
the behavior you're complaining of.

            regards, tom lane

"Jefim Matskin" <JefimM@sphera.com> writes:
> What are the rules for the order of the invocation  of triggers defined for some table?

Triggers fired for the same event are executed in alphabetical order by
trigger name --- in 7.3.  In prior releases there is no guarantee about
the firing order.

            regards, tom lane