On Thu, 2024-05-23 at 22:18 -0400, Tom Lane wrote:
> <rstander@exa.co.za> writes:
> > I've run into a strange issue with a unique index that I'm struggling to
> > understand. I've extracted the basic info to reproduce this below.
> > ...
> > This will now block until session 2 is complete. I don't understand why this
> > would block. I do know it's that unique index causing the issue, but I need
> > the unique index in place.
>
> No, it's not about the unique index. It's about the foreign key
> constraint --- if you remove that, there is no blockage. The reason
> why that's happening is that the insertions of dependent child rows
> acquire row locks on the FK-referenced tuple, to prevent that row from
> going away before the insertions commit. So when you then decide to
> UPDATE the referenced row, that blocks on the other session's row lock.
>
> I had an idea that we were bright enough to not block if the UPDATE
> doesn't change the column(s) involved in the FK, but either I'm wrong
> or that's not working in this example for some reason. It might be
> that the fact that session 1 itself also holds such a row lock is
> complicating matters.
No, the problem is the unique constraint on "checksum".
If you update a column that is part of a unique or primary key constraint,
the row lock taken will be FOR UPDATE, not FOR NO KEY UPDATE.
And the FOR UPDATE lock conflicts with the FOR KEY SHARE lock taken
by the INSERT on the referenced row.
Without the unique constraint, there would be no lock.
Yours,
Laurenz Albe