From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Russell Simpkins <russellsimpkins@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: after delete trigger behavior
Date: Wed, 22 Jun 2005 12:27:20 -0700 (PDT)
Message-ID: <20050622122313.F42989@megazone.bigpanda.com> (raw)
In-Reply-To: <20364.1119467550@sss.pgh.pa.us>
References: <BAY103-F108CECCE4CF4F95511684B5EB0@phx.gbl>
<20050622092239.T32802@megazone.bigpanda.com>
<20364.1119467550@sss.pgh.pa.us>
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)?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, tgl@sss.pgh.pa.us, russellsimpkins@hotmail.com
Subject: Re: after delete trigger behavior
In-Reply-To: <20050622122313.F42989@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox