FW: BUG in trigger and foreign keys

From: "Jefim Matskin" <JefimM(at)sphera(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: FW: BUG in trigger and foreign keys
Date: 2002-12-24 10:40:42
Message-ID: A27FEC8516051048B5B3A119BC0D8CB65B1AB5@exch2k.spheranet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;
*/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jochem van Dieten 2002-12-24 12:05:40 creating languages in the pg_catalog schema?
Previous Message Lincoln Yeoh 2002-12-24 05:04:45 Re: SQL Injection & Stored Procedures Info