Hi,
On 2020-04-21 11:19:35 -0400, Alvaro Herrera wrote:
> 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.
I wonder if there's potentially some issue with the wrong snapshot being
used for the different statements...
> I would start by taking a few dozen backtraces and comparing them to see
> if any progress is being made.
It could also be informative to look at the walstream with pg_waldump
while the problem is occuring. Would tell us about row locks acquired
during on conflict / trigger handling etc.
> The fact that this reproduces in 11.2 would seem to discard theories
> about tuple table slot changes and table AM.
Phew ;)
Greetings,
Andres Freund