Thread: Trigger and Recursive Relation ?

Hi,
I'm a Postgres newbie trying to figure out a trigger problem.  I have a
table with a recursive relationship, and I'm trying to create a trigger that
will maintain the parent child relationship when a record is deleted.  For
example, I have records where record 0 references null, record 1 references
record 0, record 2 references record1, and so on.  I created a trigger that
maintains the relationship after a deletion.  For example, if I delete
record 1 in the above example, record 2 will now point to record 0 (record
1's parent).  The scenario works fine when I individually delete records,
but when I try to delete a set of records at once, only 1/2 of the records
are deleted.  Probably something simple, but I can't figure out what's
happening.  Here's a simplified example of what I am try to do...Please
help! Thanks

Regards,
Greg Steele

CREATE TABLE recursive(
id int PRIMARY KEY,
parent int,
FOREIGN KEY (parent) REFERENCES recursive ON DELETE CASCADE
);


CREATE OR REPLACE FUNCTION delete_on_recursive_trigger_fx() RETURNS trigger
AS
$$
   BEGIN

      UPDATE recursive
      SET parent = OLD.parent
      WHERE parent = OLD.id;

      RETURN OLD;
   END;
$$
Language 'plpgsql';


CREATE TRIGGER delete_on_recursive_trigger
BEFORE DELETE ON recursive
FOR EACH ROW
EXECUTE PROCEDURE delete_on_recursive_trigger_fx();

INSERT INTO recursive(id, parent) values(1, null);
INSERT INTO recursive(id, parent) values(2, 1);
INSERT INTO recursive(id, parent) values(3, 2);
INSERT INTO recursive(id, parent) values(4, 3);

--only 1/2 of the records are deleted!
DELETE FROM recursive;

On 8/1/06, Greg Steele <gsteele@apt-cafm.com> wrote:
> CREATE TABLE recursive(
> id int PRIMARY KEY,
> parent int,
> FOREIGN KEY (parent) REFERENCES recursive ON DELETE CASCADE
> );
>
>
> CREATE OR REPLACE FUNCTION delete_on_recursive_trigger_fx() RETURNS trigger
> AS
> $$
>    BEGIN
>
>       UPDATE recursive
>       SET parent = OLD.parent
>       WHERE parent = OLD.id;
>
>       RETURN OLD;
>    END;
> $$
> Language 'plpgsql';
>
>
> CREATE TRIGGER delete_on_recursive_trigger
> BEFORE DELETE ON recursive
> FOR EACH ROW
> EXECUTE PROCEDURE delete_on_recursive_trigger_fx();
>
> INSERT INTO recursive(id, parent) values(1, null);
> INSERT INTO recursive(id, parent) values(2, 1);
> INSERT INTO recursive(id, parent) values(3, 2);
> INSERT INTO recursive(id, parent) values(4, 3);
>
> --only 1/2 of the records are deleted!
> DELETE FROM recursive;

good puzzle :-)
you have two things:
 1. 'ON DELETE CASCADE' in FK defiinition
 2. BEFORE trigger that changes FK values of some rows.

I guess that Postgres deletes one row, but before it changes "parent"
values in other rows, then it invokes 'CASCADE' logic and deletes
another rows, but doing so it tries to find, which rows have
corresponding "parent" values and... Well, you've created something
tricky :-)
You'd better get rid of CASCADE option.

--
Best regards,
Nikolay

Hi Nikolay,
Thanks for the help.  I thought you found my mistake; the 'ON DELETE
CASCADE' wasn't intended in my FK constraint.  I was really suprised when
this didn't fix the problem.  I went as far as to entirely remove the FK
constraint on 'recursive', but the problem remains.  Any other ideas or
suggestions?

Thanks,
Greg

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Nikolay
Samokhvalov
Sent: Tuesday, August 01, 2006 3:26 PM
To: Postgres Novice
Subject: Re: [NOVICE] Trigger and Recursive Relation ?


On 8/1/06, Greg Steele <gsteele@apt-cafm.com> wrote:
> CREATE TABLE recursive(
> id int PRIMARY KEY,
> parent int,
> FOREIGN KEY (parent) REFERENCES recursive ON DELETE CASCADE
> );
>
>
> CREATE OR REPLACE FUNCTION delete_on_recursive_trigger_fx() RETURNS
trigger
> AS
> $$
>    BEGIN
>
>       UPDATE recursive
>       SET parent = OLD.parent
>       WHERE parent = OLD.id;
>
>       RETURN OLD;
>    END;
> $$
> Language 'plpgsql';
>
>
> CREATE TRIGGER delete_on_recursive_trigger
> BEFORE DELETE ON recursive
> FOR EACH ROW
> EXECUTE PROCEDURE delete_on_recursive_trigger_fx();
>
> INSERT INTO recursive(id, parent) values(1, null);
> INSERT INTO recursive(id, parent) values(2, 1);
> INSERT INTO recursive(id, parent) values(3, 2);
> INSERT INTO recursive(id, parent) values(4, 3);
>
> --only 1/2 of the records are deleted!
> DELETE FROM recursive;

good puzzle :-)
you have two things:
 1. 'ON DELETE CASCADE' in FK defiinition
 2. BEFORE trigger that changes FK values of some rows.

I guess that Postgres deletes one row, but before it changes "parent"
values in other rows, then it invokes 'CASCADE' logic and deletes
another rows, but doing so it tries to find, which rows have
corresponding "parent" values and... Well, you've created something
tricky :-)
You'd better get rid of CASCADE option.

--
Best regards,
Nikolay

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster