Strange issue with unique index - Mailing list pgsql-general

From
Subject Strange issue with unique index
Date
Msg-id 420a01daad70$fcf2bd80$f6d83880$@exa.co.za
Whole thread Raw
Responses Re: Strange issue with unique index
List 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

pgsql-general by date:

Previous
From: Ron Johnson
Date:
Subject: Re: PG 12.2 ERROR: cannot freeze committed xmax
Next
From: Tom Lane
Date:
Subject: Re: Strange issue with unique index