Date: Wed, 22 Jun 2005 12:27:20 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Russell Simpkins <russellsimpkins@hotmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: after delete trigger behavior 
In-Reply-To: <20364.1119467550@sss.pgh.pa.us>
Message-ID: <20050622122313.F42989@megazone.bigpanda.com>
References: <BAY103-F108CECCE4CF4F95511684B5EB0@phx.gbl>
	<20050622092239.T32802@megazone.bigpanda.com>
	<20364.1119467550@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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)?