Re: [BUGS] BUG #14808: V10-beta4, backend abort

On Sat, Sep 16, 2017 at 7:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Attached is an updated patch that incorporates the ideas you suggested.

I was imagining that you would just need to keep a back pointer to the
last queued event for the same (relation, command), since that's the
only one you'd ever need to consider cancelling, and then no scanning
would be needed.  I am probably missing something.

> 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.

It does seem like an inconsistency that it would be good to fix, but I
don't immediately see how to make that happen with the current design.
It would be interesting to know what DB2 does here in terms of trigger
execution contexts and transition tables when you have a chain of 2, 3
and 4 foreign key referential actions.

Is it worth adding a test with an extra level of chaining in the self_ref case?

Is it worth adding tests for SET NULL and SET DEFAULT, to exercise the
complete set of referential actions?

> 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.

Makes sense.

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

+1

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs