From: <rstander@exa.co.za>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-general@postgresql.org>
References: <420a01daad70$fcf2bd80$f6d83880$@exa.co.za> <561365.1716517130@sss.pgh.pa.us>
In-Reply-To: <561365.1716517130@sss.pgh.pa.us>
Subject: RE: Strange issue with unique index
Date: Fri, 24 May 2024 09:04:35 +0200
Message-ID: <425e01daada8$a2e664d0$e8b32e70$@exa.co.za>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQI/VJhda0vQCfLX0nyYqjXQ5SpWMQEkEtpgsNOFFEA=
Content-Language: en-za
Archived-At: <https://www.postgresql.org/message-id/425e01daada8%24a2e664d0%24e8b32e70%24%40exa.co.za>
Precedence: bulk

<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.
>You can make things a little better, at the cost of more overhead, by
declaring the FK as DEFERRABLE INITIALLY DEFERRED.

Thanks for the swift response. No concern using a Deferred FK here, because
it's not a hight TPS area. And it does work tx.

What is just interesting is that this does not happen with that unique index
in place. If I run that scenario with a normal FK and without the unique
index there is also no blocking happening. So it does look like PG is smart
enough in the normal flow, but not with the unique index in place. In my
real world table I could also work around it by making the unique index
filtered to exclude this type of update (There is another flag on the
table).

This scenario does just bring up questions in other parts of our system,
because we have a few that has this structure of parent/child with unique
index on parent and updates on both levels.

Regards
Riaan Stander