On Thu, Nov 17, 2005 at 11:45:07PM -0500, Tom Lane wrote:
> This has a couple of problems:
>
> 1. You can't delete a post's children before deleting the post itself,
> because of the REFERENCES constraint. I'm not entirely sure why your
> original formulation of the trigger didn't hit that failure, but I sure
> hit it while experimenting with alternatives.
I tried with and without the constraint, as I realised I was basically
implementing the cascade myself, but it didn't make any difference to
the failure mode.
> 2. The reason the UPDATE causes a problem is that it creates row
> versions that are newer than the versions the outer DELETE can see.
> (Any database changes caused by a function invoked by a query are by
> definition later than that query.) This means that if the outer
> DELETE hasn't yet zapped a row that the UPDATE touches, it will fail to
> delete that row when it does come to it.
OK, I understand the issue now :-)
> The easiest way to fix #2 is to do the UPDATEs in an AFTER trigger
> instead of a BEFORE trigger, and the easiest way to fix #1 is to let the
> system do it for you, by using ON DELETE CASCADE instead of a
> handwritten trigger. I got reasonable behavior with this:
[snip]
This is close, but not quite suitable.
The issue is that I need to UPDATE the parent, reducing the 'replies' column by
the number of children rows I am deleting, including the sub-children.
This is why I was implementing the cascade myself in my first attempt
- it let me call an UPDATE once per DELETE, making sure we reduce the
number of replies in the parents by the correct amount.
As I've just tried without triggers (just the cascade constraint), a
'DELETE FROM post WHERE id = x' will only admit to deleting one
row. The same problem can be seen in your example:
> regression=# DELETE FROM post WHERE id = 3002;
[snip]
> NOTICE: decremented 1 parent rows of 3002
> DELETE 1
> regression=# select * from post;
> id | parent | replies
> ------+--------+---------
> 3000 | | 0
> 3001 | 3000 | -1
> (2 rows)
The 'replies' should have been reduced not by one, but by how ever
many children below it that were deleted.
I've been thinking about this a little while since your message, but
I'm not sure yet I can think of a way around it. If the AFTER delete
trigger could discover how many rows were deleted and perform the
single UPDATE, reducing 'replies' by that much, the rest would work
by virtue of a seperate UPDATE trigger.
This seems possible, but I will have to cogitate on it for a while :-)
On the face of it it seems I will have to implement the cascading DELETE
myself, and keep track of how many DELETE's I am doing.
- Justin
--
Justin Hawkins | justin@hawkins.id.au
| http://hawkins.id.au
