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 (AT) apt-cafm (DOT) com>
Date: Aug 1, 2006 11:31 PM
Subject: [NOVICE] Trigger and Recursive Relation ?
To: Postgres Novice <pgsql-novice (AT) postgresql (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Nikolay Samokhvalov" <samokhvalov (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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 (AT) sss (DOT) pgh.pa.us> wrote:

--
Best regards,
Nikolay

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 8/2/06, Nikolay Samokhvalov <samokhvalov (AT) gmail (DOT) com> wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Nikolay Samokhvalov" <samokhvalov (AT) gmail (DOT) com> writes:
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

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