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

Is this a bug or not?
Actually, ordinary person get used to think that if "delete from tbl"
ends, then there should no rows exists in tbl, but I understand that
DELETE FROM works in a loop and...

Let's take a look at the standard paper (ISO/IEC 9075-2:2003 -- 14.7
<delete statement: searched> -- General Rules):

"...
11) All rows that are marked for deletion are effectively deleted at
the end of the <delete statement: searched>,
prior to the checking of any integrity constraints.
12) If <search condition> is specified, then the <search condition> is
evaluated for each row of T prior
invocation of any <triggered action> caused by the imminent or actual
deletion of any row of T.
..."

So, is it a bug? Seems to be so..

---------- Forwarded message ----------
From: Greg Steele <gsteele@apt-cafm.com>
Date: Aug 1, 2006 11:31 PM
Subject: [NOVICE] Trigger and Recursive Relation ?
To: Postgres Novice <pgsql-novice@postgresql.org>

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;

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

"Nikolay Samokhvalov" <samokhvalov@gmail.com> writes:

Is this a bug or not?

I don't think so --- or perhaps better, this is a buggy trigger.
he UPDATE in the trigger will supersede the base DELETE query for any
rows that the UPDATE changes before the base DELETE has reached 'em.
Essentially you've written an indeterminate system ...

regards, tom lane

OK, then we should at least forbit making such things... Otherwise, it
seems to be smth like gotcha.

But look at this please:

"12) If <search condition> is specified, then the <search condition> is
evaluated for each row of T prior
invocation of any <triggered action> caused by the imminent or actual
deletion of any row of T."

Does Postgres work this way? In the case of 'delete from tbl;' we
have search condition>=TRUE for all rows. If we evaluate it *before*
any other operation, we should mark all rows to be deleted. I guess,
Postgres doesn't follow this logic..

Am I wrong?

P.S. BTW, look at the -novice list - he reports, that problem remains
even after dropping FK at all.

On 8/2/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Nikolay Samokhvalov" <samokhvalov@gmail.com> writes:

Is this a bug or not?

I don't think so --- or perhaps better, this is a buggy trigger.
he UPDATE in the trigger will supersede the base DELETE query for any
rows that the UPDATE changes before the base DELETE has reached 'em.
Essentially you've written an indeterminate system ...

regards, tom lane

--
Best regards,
Nikolay

On 8/2/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:

Does Postgres work this way? In the case of 'delete from tbl;' we
have search condition>=TRUE for all rows. If we evaluate it *before*
any other operation, we should mark all rows to be deleted. I guess,
Postgres doesn't follow this logic..

My assumption: Postgres takes one row, marks it as deleted, then
executes trigger and updates another row. Due to MVCC new version of
that row is created and in the following iteration Postgres simply
doesn't "see" this row...

I don't understand how this can be called "not bug"... Please, help me
understand it :-)

--
Best regards,
Nikolay

"Nikolay Samokhvalov" <samokhvalov@gmail.com> writes:

I don't understand how this can be called "not bug"... Please, help me
understand it :-)

The situation is that the DELETE arrives at a row after the trigger has
already UPDATEd that row. You could make a reasonable case for throwing
an error in this situation, but what we choose to do is assume that the
trigger's action is correct. The row version that the DELETE would have
acted on no longer "exists", so I don't really see that this violates the
spec (bearing in mind that the spec doesn't know what MVCC is).

I think the subtext of your complaint is that you'd like the DELETE to
be applied to the updated row, but that doesn't hold any more water than
what we do now. Consider the opposite case where the outer query is an
UPDATE and the trigger DELETEs a row that the outer query will reach
later --- it certainly isn't going to make sense to un-delete the row
so we can update it. The only sensible choices here are to throw an
error or do nothing.

regards, tom lane