Thomas Munro <thomas.munro@enterprisedb.com> writes:
> What is going on here?
> ...
> insert into foo values (1, null), (2, 1);
>
> postgres=# delete from foo where a = 1;
> NOTICE: trigger = foo_r_trig, old table = (1,), depth = 1
> NOTICE: trigger = foo_s_trig, old table = (1,), depth = 1
> NOTICE: trigger = foo_r_trig, old table = (2,1), depth = 1
> NOTICE: trigger = foo_s_trig, old table = (2,1), depth = 1
> NOTICE: trigger = foo_s_trig, old table = <NULL>, depth = 1
> DELETE 1
OK, now that I'm a little more awake, I looked at this, and I think it's
actually an instance of the "closed transition table" behavior you were
asking about. Initially, we perform the delete of the (1,null) row,
and that queues AFTER ROW triggers -- both the RI enforcement one and
the explicit foo_r_trig one -- and puts the row into the transition table.
When the executor finishes that scan it queues the foo_s_trig AFTER
STATEMENT trigger. Then we start to execute the triggers, and as I have
the patch now, it first marks all the transition tables closed. I think
that the RI enforcement trigger fires before foo_r_trig on the basis of
name order, but it doesn't actually matter if it fires first or second.
Either way, it causes a cascaded delete of the (2,1) row, and again
we queue AFTER ROW triggers and put the row into the transition table.
But now, since the first transition table was already marked closed,
we create a new transition table and put (2,1) into it. And then we
queue foo_s_trig, and since we're looking at a new (not closed)
AfterTriggersTableData struct, that's allowed to happen. Then we
fire foo_r_trig and foo_s_trig referencing the original transition
table, which produce the first two NOTICE lines. Then we repeat
this entire process with the newly queued triggers. The second
invocation of the RI enforcement trigger doesn't find any rows to
delete, but it nonetheless causes queuing of a third AFTER STATEMENT
trigger, which eventually gets to run with an empty transition table.
So this is a bit annoying because we're marking the transition table
closed before the RI triggers can have the desired effects. I wonder
if we can rejigger things so that the tables are only closed when
actually used.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs