Re: 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