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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1srkX5-004D9V-Mk
	for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 20:47:16 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1srkX3-00CVXi-JR
	for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 20:47:14 +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 <tgl@sss.pgh.pa.us>)
	id 1srkX3-00CVXZ-7R
	for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 20:47:14 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1srkX2-000ESh-3T
	for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 20:47:13 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 48KKl89U2587193;
	Fri, 20 Sep 2024 16:47:08 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: veem v <veema0000@gmail.com>
cc: Adrian Klaver <adrian.klaver@aklaver.com>,
        Greg Sabino Mullane <htamfids@gmail.com>,
        Christophe Pettus <xof@thebuild.com>,
        pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: IO related waits
In-reply-to: <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com> <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com> <CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com> <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com> <CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com> <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com> <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com> <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com> <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com> <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com> <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com> <bc8363b5-28d0-4218-b3d6-73c7d78717da@aklaver.com> <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
Comments: In-reply-to veem v <veema0000@gmail.com>
	message dated "Sat, 21 Sep 2024 01:31:23 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2587191.1726865228.1@sss.pgh.pa.us>
Date: Fri, 20 Sep 2024 16:47:08 -0400
Message-ID: <2587192.1726865228@sss.pgh.pa.us>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/2587192.1726865228%40sss.pgh.pa.us>
Precedence: bulk

veem v <veema0000@gmail.com> writes:
> Able to reproduce this deadlock graph as below.  Now my question is , this
> is a legitimate scenario in which the same ID can get inserted from
> multiple sessions and in such cases it's expected to skip that (thus "On
> conflict Do nothing" is used) row. But as we see it's breaking the code
> with deadlock error during race conditions where a lot of parallel threads
> are operating. So how should we handle this scenario?

Do you have to batch multiple insertions into a single transaction?
If so, can you arrange to order them consistently across transactions
(eg, sort by primary key before inserting)?

			regards, tom lane