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 1srknw-004EvP-L3 for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 21:04:41 +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 1srknu-00CbZk-KN for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 21:04:39 +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 1srknu-00CbZQ-80 for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 21:04:39 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1srknt-000EYx-3f for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 21:04:38 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-5df9433ac0cso1237299eaf.3 for ; Fri, 20 Sep 2024 14:04:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726866276; x=1727471076; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=/RquWb/BUX3xvJyN57w8Aii2qVBKgR3TMBv/wjPU0e4=; b=c6zfJvzADW+eVPNDxcRrxe4YUwEQnsBT02zaFvNNJF8B+f/yxS4YFXP8X/RALXgeMJ 92EmSJu67HMxxlBo812rgQDI9k7Rhv/jxpGCCF3Gs87vjia/CiIgNjr42ti3IZwENi0V 8sBt+txdcEPnQ4Xni39/HFzSFEIU+r9AuPc0SY0rH2FPISAK+qZW4JFdfchA6jdLoZBk rVv1FdcqkkzQ0DdLuWNYS6RxzHsJGI58Ej4ME9ds95asXk9QoY7wdoMQtc40SiYujoME 47r5AyKqH6F1fhm4pz0UzLJra8+37gXWls85Brt7Agwvp2FvJ/FM2FGK9svRtsA8um+c lH8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726866276; x=1727471076; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=/RquWb/BUX3xvJyN57w8Aii2qVBKgR3TMBv/wjPU0e4=; b=Ft7tok2/dT8vk+WJM9GnX8vrI+d9bfNpAZT3QYUZfcs4YnGNPQUZJ4nXus8ToTIDI5 CovnH1aMNqpZmmtMSjLFBvjSBKVnBlOLmXctuy64Haeqo5ba4/J9wZCwkhvLjKph7lbB igtvp7g3EsyD1XWX26dI+8VenppznoAdvZa5ZdPmRMO/GBz/TqSSK4bv1Cht3VbS2pae 8LrQhMmXwbHvT8+vnidrWop40qyzOX+syX5QYNTYavD1wqHF/ZhNUNIyiShUZPJH7sYq 9NGR8LbNSNo/3pF41K267cVbzCL6RrXjLw5WcdSKmL+6vaxiIxbpfKLhcbUcw42QPiVa R+UQ== X-Gm-Message-State: AOJu0YzD34/yVTSxjFYn20VKQS0pNX3Ml+haG9t3SHPp9qTDkm/u5qhU GiNNxMV/FTqzVix/NJNDq6uNd6Ym1D8nqwfIT5iwsN0R/VbVAUC9RATCduznDQuW1OApYk3T6Bf imFJMrNreWyFy/7al48qfPeyrpDDHKA== X-Google-Smtp-Source: AGHT+IFlYVr8c4Kl0FOj0PX39kx7+3vROwzJOWvutIN/BdYuJSofCgcWjemj9l6/GFX+TW42LIw4lGZkwjH+VoPbFKE= X-Received: by 2002:a05:6820:210:b0:5dc:cdf8:21c8 with SMTP id 006d021491bc7-5e58ba5d303mr2731400eaf.4.1726866275931; Fri, 20 Sep 2024 14:04:35 -0700 (PDT) MIME-Version: 1.0 References: <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com> <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com> <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com> <2587192.1726865228@sss.pgh.pa.us> In-Reply-To: <2587192.1726865228@sss.pgh.pa.us> From: Ron Johnson Date: Fri, 20 Sep 2024 17:04:24 -0400 Message-ID: Subject: Re: IO related waits To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000f3a4aa06229362e8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f3a4aa06229362e8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Sep 20, 2024 at 4:47=E2=80=AFPM Tom Lane wrote: > veem v 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 "O= n > > 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)? > That's exactly what I did back in the day. Because of database buffering, sorting the data file at the OS level made the job 3x as fast as when the input data was random. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. crustacean! --000000000000f3a4aa06229362e8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Sep 20, 2024 at 4:47=E2=80=AFPM T= om Lane <tgl@sss.pgh.pa.us> = wrote:
veem v <veema0000@gmail.com> writes:
> Able to reproduce this deadlock graph as below.=C2=A0 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 (th= us "On
> conflict Do nothing" is used) row. But as we see it's breakin= g the code
> with deadlock error during race conditions where a lot of parallel thr= eads
> 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)?
=C2=A0
That's exactly what I did back in the day.=C2=A0 Because of data= base buffering, sorting the data file at the OS level made the job 3x as fa= st as when the input data was random.

--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> crus= tacean!
--000000000000f3a4aa06229362e8--