Strange issue with unique index

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

Responses

Browse pgsql-general by date

  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