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