public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Subject: Strange issue with unique index
Date: Fri, 24 May 2024 02:26:15 +0200
Message-ID: <[email protected]> (raw)

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Strange issue with unique index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox