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 update the tree structure).
You should also note that all the trigger invocations other that the first one are caused by the delete that is issued in the 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's and 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
Import Notes
Resolved by subject fallback
"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
Import Notes
Resolved by subject fallback
"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