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 |
