Strange issue with unique index
Good day
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.
PG Version: 15.6
--Create structure
CREATE SCHEMA IF NOT EXISTS playground;
CREATE TABLE playground.parent (
parent_id bigint PRIMARY KEY,
checksum text,
description varchar(100) NOT NULL
);
CREATE TABLE playground.child (
child_id bigint PRIMARY KEY,
description varchar(100) NOT NULL,
parent_id bigint NOT NULL references playground.parent(parent_id)
);
CREATE UNIQUE INDEX ON playground.parent(checksum);
--Insert one parent entry
INSERT INTO playground.parent VALUES (1, 'mychecksum', 'parent1');
Now open two separate psql prompts
On session 1
BEGIN;
INSERT INTO playground.child VALUES (1, 'child1', 1);
On session 2
BEGIN;
INSERT INTO playground.child VALUES (2, 'child2', 1);
On session 1
UPDATE playground.parent SET checksum = 'newchecksum' WHERE parent_id = 1;
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.
If I now run the following statement on session 2 it causes a deadlock
UPDATE playground.parent SET checksum = 'anothernewchecksum' WHERE parent_id
= 1;
Again not sure why a deadlock. I would expect a block here.
Regards
Riaan Stander
<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.
You can make things a little better, at the cost of more overhead,
by declaring the FK as DEFERRABLE INITIALLY DEFERRED.
regards, tom lane
<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
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