public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Strange issue with unique index
Date: Thu, 23 May 2024 22:18:50 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

<[email protected]> 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.

You can make things a little better, at the cost of more overhead,
by declaring the FK as DEFERRABLE INITIALLY DEFERRED.

			regards, tom lane

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Strange issue with unique index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox