Search Postgresql Archives

Strange issue with unique index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux