Re: BUG #14808: V10-beta4, backend abort

I wrote:
> Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)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.

Attached is an updated patch that incorporates the ideas you suggested.
I added an extended version of this example, which has an additional
level of FK cascade happening:

insert into self_ref values (1, null), (2, 1), (3, 2);
delete from self_ref where a = 1;
NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1)
NOTICE: trigger = self_ref_r_trig, old table = (1,), (2,1)
NOTICE: trigger = self_ref_s_trig, old table = (1,), (2,1)
NOTICE: trigger = self_ref_r_trig, old table = (3,2)
NOTICE: trigger = self_ref_s_trig, old table = (3,2)

What happens here is that the outer delete queues AR triggers for
RI enforcement and self_ref_r_trig, plus an AS trigger for
self_ref_s_trig. Then the RI enforcement trigger deletes (2,1)
and queues AR+AS triggers for that. At this point the initial
transition table is still open, so (2,1) goes into that table,
and we look back and cancel the previous queuing of self_ref_s_trig.
Now it's time for the first firing of self_ref_r_trig, and so now
we mark the transition table closed. Then we skip the cancelled
self_ref_s_trig call, and then it's time for the second RI enforcement
trigger to fire, which deletes (3,2) but has to put it into a new
transition table. Again we queue AR+AS triggers, but this time we
can't cancel the preceding AS call. Then we fire self_ref_r_trig
again (for the (2,1) row), and then fire self_ref_s_trig; both of
them see the same transition table the first self_ref_r_trig call
did. Now it's time for the third RI enforcement trigger; it finds
nothing to delete, so it adds nothing to the second transition table,
but it does queue an AS trigger call (canceling the one added by the
second RI trigger). Finally we have the AR call queued by the second
RI trigger, and then the AS call queued by the third RI trigger,
both looking at the second transition table.

This is pretty messy but I think it's the best we can do as long as
RI actions are intermixed with other AFTER ROW triggers. Maybe with
Kevin's ideas about converting RI actions to be statement-level,
we could arrange for all three deletions to show up in one transition
table ... but I don't know how we cause that to happen before the
user's AFTER ROW triggers run. In any case, nothing will look odd
unless you have AR triggers using transition tables, which seems like
a niche usage case in the first place.

I also realized that we could undo the bloat I added to
AfterTriggerSharedData by storing a pointer to the AfterTriggersTableData
rather than the tuplestores themselves.

I feel that this is probably committable, except that I still need
to look for documentation changes.

regards, tom lane