Trouble with recursive trigger

Hi all,

I am writing a bulletin board style system, which stores posts in a
hierachy.

Each post has a parent (or NULL for a top level post).

For efficiency, I'm storing a number of replies on each post, which
shows the total replies a post has, including all sub (and sub sub,
and sub sub sub etc) posts. I update this number via a trigger.

I'm having trouble with the DELETE. When deleting a row three things
need to happen:

o recursively DELETE all children posts to preserve data integrity
o decrement the number of replies of the parent post (if it exists)
o delete itself

However, only the ultimate child (the post with no children posts)
gets deleted, despite the debugging NOTICE's seeming to indicate that
the right thing is happening.

I've discovered the cause of the problem, but I'm not sure why it's a
problem, or how to solve it.

Here's the stripped back test example:

-- ---------------------------------------------------------------

CREATE TABLE post (
id SERIAL NOT NULL PRIMARY KEY,
parent INT REFERENCES post(id),

replies INT NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION post_update_replies() RETURNS TRIGGER AS $function$
BEGIN
IF (TG_OP = 'DELETE') THEN
-- delete any children of this post, this will recurse of course
RAISE NOTICE 'deleting any that have a parent of % so I can delete %', OLD.id, OLD.id;
DELETE FROM post WHERE parent = OLD.id;

-- now update the parents replies, if they have any
IF (OLD.parent IS NOT NULL) THEN
RAISE NOTICE 'decrementing replies of parent % because of delete of %', OLD.parent, OLD.id;
UPDATE post SET replies = replies - 1 WHERE id = OLD.parent;
END IF;

RAISE NOTICE 'actually deleting % now', OLD.id;
RETURN OLD;
END IF;
END;
$function$ LANGUAGE plpgsql;

CREATE TRIGGER post_update_replies BEFORE DELETE ON post
FOR EACH ROW EXECUTE PROCEDURE post_update_replies();

COPY post FROM stdin WITH CSV;
3000,,0
3001,3000,0
3002,3001,0
3003,3002,0
3004,3003,0
3005,3004,0
3006,3005,0

-- ---------------------------------------------------------------

(I've trimmed out the UPDATE and INSERT handler parts of the trigger,
so ignore the fact there is code missing to increment 'replies').

If you do something like:

DELETE FROM post WHERE id = 3002;

You will see that only the post with id of 3006 gets deleted, despite
the NOTICE's indicating the right things are happening.

If you repeat the DELETE, you will delete 3005 (which now has no
children), repeat it again and 3004 disappears, and so on.

I've pinpointed it to the UPDATE. If I comment out the UPDATE in the
trigger function above, the recursive delete works correctly.

It's like the UPDATE stops that iteration of the trigger from working
correctly - it obviously isn't stopping it, as the debugging shows
execution continues, but it somehow makes the 'RETURN OLD;' statement
not have any effect.

Here's the debugging output when I do a DELETE:

justin=> delete from post where id = 3002;
NOTICE: deleting any that have a parent of 3002 so I can delete 3002
NOTICE: deleting any that have a parent of 3003 so I can delete 3003
NOTICE: deleting any that have a parent of 3004 so I can delete 3004
NOTICE: deleting any that have a parent of 3005 so I can delete 3005
NOTICE: deleting any that have a parent of 3006 so I can delete 3006
NOTICE: decrementing replies of parent 3005 because of delete of 3006
NOTICE: actually deleting 3006 now
NOTICE: decrementing replies of parent 3004 because of delete of 3005
NOTICE: actually deleting 3005 now
NOTICE: decrementing replies of parent 3003 because of delete of 3004
NOTICE: actually deleting 3004 now
NOTICE: decrementing replies of parent 3002 because of delete of 3003
NOTICE: actually deleting 3003 now
NOTICE: decrementing replies of parent 3001 because of delete of 3002
NOTICE: actually deleting 3002 now
DELETE 0

Interestingly, I see the following other debugging information among
the above NOTICE's, I'm not sure if it's related to this problem. It
doesn't seem to be an error, but I don't know the source of it:

justin=> delete from post where id = 3002;
NOTICE: deleting any that have a parent of 3002 so I can delete 3002
NOTICE: deleting any that have a parent of 3003 so I can delete 3003
CONTEXT: SQL statement "DELETE FROM post WHERE parent = $1 "
PL/pgSQL function "post_update_replies" line 11 at SQL statement
NOTICE: deleting any that have a parent of 3004 so I can delete 3004
CONTEXT: SQL statement "DELETE FROM post WHERE parent = $1 "
PL/pgSQL function "post_update_replies" line 11 at SQL statement
SQL statement "DELETE FROM post WHERE parent = $1 "
PL/pgSQL function "post_update_replies" line 11 at SQL statement
NOTICE: deleting any that have a parent of 3005 so I can delete 3005

[snip]

I'm guessing that the DELETE is doing something to the rows, even
before the trigger returns for that iteration causing subsequent
UPDATE's to break in some subtle way.

Any ideas (or workarounds) most appreciated :-)

Pg version is 8.1.0, tried also on 8.0.3 with no difference.

- Justin

--
Justin Hawkins | justin(at)hawkins(dot)id(dot)au
| http://hawkins.id.au