FW: BUG in trigger and foreign keys - Mailing list 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;
*/