Обсуждение: Fwd: [NOVICE] Trigger and Recursive Relation ?
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
"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