Re: Trigger and Recursive Relation ?

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