Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sAIlc-008qTK-A7 for pgsql-general@arkaria.postgresql.org; Fri, 24 May 2024 00:26:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sAIlc-000PsV-9H for pgsql-general@arkaria.postgresql.org; Fri, 24 May 2024 00:26:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sAIlb-000PsN-6f for pgsql-general@lists.postgresql.org; Fri, 24 May 2024 00:26:39 +0000 Received: from outgoing36.cpt4.host-h.net ([196.40.103.46]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sAIlT-001hOY-Hm for pgsql-general@postgresql.org; Fri, 24 May 2024 00:26:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=exa.co.za; s=xneelo; h=Content-Type:MIME-Version:Message-ID:Date:Subject:To:From: reply-to:sender:cc:bcc:in-reply-to:references:content-transfer-encoding; bh=eRREGZxQL/DAkEc2se8bmu6p0Jq5a3FEVMa2bQDheNo=; b=h9pO+/aPDt6GioDsokSXCnVHuo 0kyPN7lAxy+XL08YSD+cqr3Gxj4HQlf+LDU5fueEsD+m8AENom/ryfqUSVAoAY2JLi4joe+zHH71S 0rhBeRXC0gaLA2q0YHATzpqc0gyK3cGAD5a6JKRVbqkoHu4kij9dQaIadcOqZEtjAvYRQe6RxMXWY mqlF1bMpJkHXY4Sf/SmqI4TMZu2ng3Tj/eg4cWXlANZyBkRzb/gfYk+wc4xC4l09TN8Z+6bhGSxan +RJKYb/Aj+LLC5h+qi60JwoKQlxkLdzHGvlY0Dg+gS5fCJ9slKHpbfbweZ9cKQRJLdEshx0xkpRWZ OFtHAhAg==; Received: from www20.cpt1.host-h.net ([197.221.2.20]) by antispam5-cpt4.host-h.net with esmtpsa (TLSv1.3:TLS_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1sAIlI-001ldI-Rv for pgsql-general@postgresql.org; Fri, 24 May 2024 02:26:25 +0200 Received: from [155.93.228.209] (helo=DMZHOST) by www20.cpt1.host-h.net with esmtpa (Exim 4.92) (envelope-from ) id 1sAIlG-000Hz7-JR for pgsql-general@postgresql.org; Fri, 24 May 2024 02:26:18 +0200 Received: from M6800 (M6800 [10.0.0.22]) by DMZHOST with ESMTPSA (version=TLSv1.2 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256) ; Fri, 24 May 2024 02:26:15 +0200 From: To: Subject: Strange issue with unique index Date: Fri, 24 May 2024 02:26:15 +0200 Message-ID: <420a01daad70$fcf2bd80$f6d83880$@exa.co.za> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_420B_01DAAD81.C07C02B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: Adqtb4swwyagqHyRQB+w6FcWJvE6xA== Content-Language: en-za X-Authenticated-Sender: smtprelay@exa.co.za X-Virus-Scanned: Clear X-Originating-IP: 197.221.2.20 X-SpamExperts-Domain: exa.co.za X-SpamExperts-Username: Authentication-Results: host-h.net; auth=pass (login) smtp.auth=@exa.co.za X-SpamExperts-Outgoing-Class: ham X-SpamExperts-Outgoing-Evidence: Combined (0.16) X-Recommended-Action: accept X-Filter-ID: Pt3MvcO5N4iKaDQ5O6lkdGlMVN6RH8bjRMzItlySaT/WLAEUmWO931a3b5jqDrjDPUtbdvnXkggZ 3YnVId/Y5jcf0yeVQAvfjHznO7+bT5w5z7jbJ+CihFj4DB2gu2tChCCsYSwE98KYIQd3xFv6NY9w knWGobBIYos3d1n2bRHIJWd3Xul8/t5NOGAH/uaz5WCy5VkstzhnxgTXqsdmp+1Wwovo7K2F1QCX io2EcTx+hJyAsgQN3+fJ25MXybLTCTJ4XEDT+1HSMHFpKmf52mDqJKDcouZiQZR+V628Ua4tmID6 jXkVn2E3pOllDDDOYlwzjvDZ/FkpvR7cwSxN+RTHswbbB/ha+ZWrSAi8Skyxe17QIfgTGOgXzm19 /3QDUdShXBqjIqKx5jnB7z3Yhhp3eWzGQOuNjEBo/WEbPRDIZwKDK1Qy7Yn0clcDVSjgqllwqh46 uPZdiltO4MYeQPg1X+l8/90KyFbMvEpHQ72U/86+VeHQfEPfz3YUMMqBqSIGAS5g6SocktP6HR2V 1Mnsv/4cfDtSkJz6hRSdrQzTHuxweXeDMpjWlleKrN32mP5wriU8jSHrtsnI5JD0GxR9Ovbqz/k9 Jlx8RTZkJCspOMQJvQ/Ck3iiU+4DQAj366V+bW1tsfg2xPA2CLYwNwRJK5SfGCU5urMEcZjHTSIO QBSe27uNTHZUK2fdkYWNQXYp4FS8cjFOL4ruJA9IhurIIltEJAcLrdm5Lzw2Bzfn9sfTcvc8Pdr1 Eep4DMP1lKJ7jrNRXsNt/cBtRLSZF5PKz2yNdDDsBk2LFqcnZWtWyXwDxReKVx7Gw7dyNG9JpylL Y37Aq/QkmLlytI8S9SVqTUhhpQ655SdhphvEJggccBIk1Sag4dKiqCrF8eZZf8jM/pn5Bs/VD59c 49qjzM5esf5HvTE9CdhvgqA8uVgS3OfHM/Z7VPRBWdsQUE3hDWLh6pxGIYslV6IXh2J1rQ97Bvm6 U3nD3jtNJ8DXdlOuJOSn6FAlGPXmAYLEQx1jwaPVj4uCBOOSfEjCAVrAOzTmDyM+MpzGu6WUOIYv xxowBYbMtXBJQROtDH/rAH/E X-Report-Abuse-To: spam@antispamquarantine.host-h.net List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_420B_01DAAD81.C07C02B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_420B_01DAAD81.C07C02B0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D 'newchecksum' WHERE parent_id =3D = 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 =3D = 'anothernewchecksum' WHERE parent_id =3D 1;

 

Again not sure why a deadlock. I = would expect a block here.

 

Regards

Riaan = Stander

------=_NextPart_000_420B_01DAAD81.C07C02B0--