Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Apr-21, Tom Lane wrote:
>> A variant of that theory is that foreign key trigger firings are being
>> skipped in one case but not the other; but offhand I think those
>> optimizations only apply to update/delete cases not inserts. Anyway
>> that still requires some assumptions about moving parts that you
>> haven't shown us.
> I wonder if there are any funny interactions between trigger tuple
> acquisition and the ON CONFLICT stuff. The only thing that occurs to me
> to explain the fact that it only fails with the two stmts in the DO
> block is that the second insert can see rows as inserted by the same
> transaction.
Yeah, I'll bet a good deal that the use of ON CONFLICT is part of the
issue here. That can queue firings of the unique_key_recheck trigger,
but I'm too lazy to go figure out exactly when that happens or gets
optimized away. (AfterTriggerSaveEvent definitely does know about
optimizing it away in some cases...)
regards, tom lane