On Wed, 22 Jun 2005, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> >> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
> >> PROCEDURE resort_test1();
>
> > I think this will work in an after delete trigger, but not in a before
> > delete trigger (and seems to in my tests). I'm not sure what the spec says
> > about the visibility of rows in cases like this.
>
> Well, the actual effect is that the first trigger's UPDATE changes all
> the rows that the DELETE might later delete, thus overriding the delete.
> (A query cannot modify rows already modified by commands started later
> in the same transaction, such as commands issued by triggers fired by
> the query itself.)
>
> Depending on the order that the DELETE hits the rows in, there might be
> more than one row that can get processed before the UPDATEs have touched
> all remaining rows, so this is all pretty messy and not to be relied on.
>
> I suspect that if you read the spec carefully it would want a "triggered
> data change violation" error raised here. My advice is not to use a
> BEFORE trigger for this.
>
> Even an AFTER trigger will have some pretty significant problems with
> this, I'm afraid, because of the uncertainty about the order in which
> the rows are deleted (and hence the order in which the trigger instances
> fire). For instance, suppose you delete the rows with c=1 and c=2, and
> they get visited in that order. The UPDATE for c=1 will update the row
> currently having c=3 to c=2 ... whereupon that row will NOT be seen as
> an update candidate by the UPDATE for c=2. (You could work around that
> case by using ">= OLD.c" instead of "> OLD.c", but it could still fail
> with more than 2 rows being deleted.) The proposed trigger only works
> cleanly if the rows are deleted in decreasing order of c, and there's no
> very easy way to guarantee that.
Is there anything we have right now that will handle this kind of thing
without requiring either updating all the counts after a deletion in a
statement trigger or once per row updating all the counts for records with
the same "a" (doing something like make a sequence and using it in a
subselect matching keys)?