From: | <rstander(at)exa(dot)co(dot)za> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Strange issue with unique index |
Date: | 2024-05-24 00:26:15 |
Message-ID: | 420a01daad70$fcf2bd80$f6d83880$@exa.co.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-05-24 02:18:50 | Re: Strange issue with unique index |
Previous Message | Ron Johnson | 2024-05-23 23:17:29 | Re: PG 12.2 ERROR: cannot freeze committed xmax |