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