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 1srDI9-00G8Gx-QM for pgsql-general@arkaria.postgresql.org; Thu, 19 Sep 2024 09:17:37 +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 1srDI8-007mDe-DP for pgsql-general@arkaria.postgresql.org; Thu, 19 Sep 2024 09:17:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srDI8-007mDM-0n for pgsql-general@lists.postgresql.org; Thu, 19 Sep 2024 09:17:36 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1srDI5-001yMh-TV for pgsql-general@lists.postgresql.org; Thu, 19 Sep 2024 09:17:35 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-a8d29b7edc2so73905266b.1 for ; Thu, 19 Sep 2024 02:17:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726737453; x=1727342253; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=gmfDd4/X6rROH2eY2MhwZJIk51wTAarXx3FCmPm/yT0=; b=j0C0Clt490XheRYW+sZ1cixBiA6exN+h1xddERW6DnRb04YwPcqnuYotPs+Y+gOKaz 5plij5IsZ/Yc6EfTP+84zOT1kTa0iSt3wc2DdBqn/cv8bjf+lNDK2k9mfcPsAwdFvONu DH3yK4Cadp4DGLBTMTt5QLaie68dv+4LF8tS/KNVa/St7/nqKED0R5OkU6dxRu2hDHm2 lxz5S1dKYGPDlZYvZKr2keimeDxK93PYVsP0pU3Neg59RliPMts5dzL1jrUN6sM3WwMH if0Y6j0z65EscFksE6MZvR0rvnikPL3Gg/D/XQqez4/ADHyfMeTGpHPPqKPV+cG55m0P 45sQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726737453; x=1727342253; h=cc: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=gmfDd4/X6rROH2eY2MhwZJIk51wTAarXx3FCmPm/yT0=; b=QGUc+ok3XLiY+arGFjmqf+SyCtA8DfWvO+OeM39g0xKoKxkz1/A4dzx9yO1yRoQqmm BoQHDD19xa/CVJt9Qvm0VUoOTX3JetajOWkjbbFmelMm3Z+5jER9XhTFQi7d88KJbb7J ezP4QDxluJtsAv/m3bOFCszzw4qAg11gZhom4yePDb8YKPDgNsOw6E+18zVA56muD6hV oERI4xoy7rY8XjIpyqoQy7grQQQJFzXLl+mrY8PT26OwPqySb5kDn1R1zhTra75hXB/g VjB5lWz38/RkxKUleyGH/1GeZqicSStJVp70BAt6AXiUU28GTDGdBD2Tiu8cvwcjlOJu gdMA== X-Forwarded-Encrypted: i=1; AJvYcCUa1K/b/0ANz32fqmylJOvW5Ntl2Enr+xholT9NM4gux1Zn5XYuhOTJPHn0AJ2AtLEFRoAPkD72DVnCWgoO@lists.postgresql.org X-Gm-Message-State: AOJu0Yyj/9H6foP4iVyCw80T6+U+Y5QidQopTP/VzSYVj2SNKMVcFC91 IZPIgYJXzBdpaIMtl1YuOz9d25R1aR4gD7rGjqC8CmpRFTyJbCZaUTTF0yEKNlxs0r6BECoGvIW ndg+4ahZOWIKpokegJAqhJ+GoYxY= X-Google-Smtp-Source: AGHT+IGgB9goevEHP9osUdAl/zua38ceNGIahnCkDc15ZlBTX0wpFt8H4dKBkWHcxxewuTRLHyQBP/7siTAilgLL1KQ= X-Received: by 2002:a17:907:7b8a:b0:a90:b67e:7aa9 with SMTP id a640c23a62f3a-a90b67e809dmr450330666b.55.1726737452703; Thu, 19 Sep 2024 02:17:32 -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> In-Reply-To: <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com> From: veem v Date: Thu, 19 Sep 2024 14:47:21 +0530 Message-ID: Subject: Re: IO related waits To: Adrian Klaver Cc: Greg Sabino Mullane , Christophe Pettus , pgsql-general Content-Type: multipart/alternative; boundary="0000000000007d2b2c0622756474" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007d2b2c0622756474 Content-Type: text/plain; charset="UTF-8" On Thu, 19 Sept 2024 at 03:02, Adrian Klaver wrote: > > > This needs clarification. > > 1) To be clear when you refer to parent and child that is: > FK > parent_tbl.fld <--> child_tbl.fld_fk > > not parent and child tables in partitioning scheme? > > 2) What are the table schemas? > > 3) What is the code that is generating the error? > > > Overall it looks like this process needs a top to bottom audit to map > out what is actually being done versus what needs to be done. > > > Yes the tables were actually having parent and child table relationships, not the child/parent table in partitioning scheme. And the PK and FK are on columns - (ID, part_date) .The table is the daily range partitioned on column part_date. *The error we are seeing is as below in logs:-* deadlock detected 2024-09-18 17:05:56 UTC:100.72.10.66(54582):USER1@TRANDB:[14537]:DETAIL: Process 14537 waits for ShareLock on transaction 220975629; blocked by process 14548. Process 14548 waits for ShareLock on transaction 220975630; blocked by process 14537. Process 14537: INSERT INTO TRANDB.PART_TAB (ID, part_date....) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING Process 14548: INSERT INTO TRANDB.PART_TAB (ID, part_date, ...) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:HINT: See server log for query details. 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:CONTEXT: while inserting index tuple (88814,39) in relation "PART_TAB_p2024_08_29" 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT: INSERT INTO TRANDB.PART_TAB (ID, part_date, ...) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: current transaction is aborted, commands ignored until end of transaction block 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT: INSERT INTO TRANDB.EXCEP_TAB (...) 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: current transaction is aborted, commands ignored until end of transaction block 2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT: 2024-09-18 17:05:56 UTC:100.72.22.33(36096):USER1@TRANDB:[14551]:ERROR: current transaction is aborted, commands ignored until end of transaction block ********* 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:ERROR: deadlock detected 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:DETAIL: Process 17456 waits for ShareLock on transaction 220978890; blocked by process 17458. Process 17458 waits for ShareLock on transaction 220978889; blocked by process 17456. Process 17456: INSERT INTO TRANDB.PART_TAB (ID, part_date, ...) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING Process 17458: INSERT INTO TRANDB.PART_TAB (ID, part_date, ..) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:HINT: See server log for query details. 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:CONTEXT: while inserting index tuple (88875,13) in relation "PART_TAB_p2024_08_29" 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:STATEMENT: INSERT INTO TRANDB.PART_TAB (ID, part_date,..) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:LOG: could not receive data from client: Connection reset by peer 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:LOG: disconnection: session time: 0:08:37.154 user=USER1 database=TRANDB host=XXXXX port=58778 --0000000000007d2b2c0622756474 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Thu, 19 Sept 2024 at 03:02, Adrian Kla= ver <adrian.klaver@aklaver.= com> wrote:


This needs clarification.

1) To be clear when you refer to parent and child that is:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FK
parent_tbl.fld <--> child_tbl.fld_fk

not parent and child tables in partitioning scheme?

2) What are the table schemas?

3) What is the code that is generating the error?


Overall it looks like this process needs a top to bottom audit to map
out what is actually being done versus what needs to be done.



Yes the tables were actually havin= g parent and child table relationships, not the child/parent table in parti= tioning scheme.=C2=A0 And the PK and FK are on columns - (ID, part_date) .T= he table is the daily range partitioned on column part_date.

=
The error we are seeing is as below in logs:-
<= br>
deadlock detected
2024-09-18 17:05:56 UTC:100.72.10.66(545= 82):USER1@TRANDB:[14537]:DETAIL: =C2=A0Process 14537 waits for ShareLock on= transaction 220975629; blocked by process 14548.
Process 14548 waits fo= r ShareLock on transaction 220975630; blocked by process 14537.
Process = 14537: INSERT INTO TRANDB.PART_TAB (ID, part_date....) =C2=A0VALUES ($1, $2= , $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, par= t_date) DO NOTHING
Process 14548: INSERT INTO TRANDB.PART_TAB (ID, part= _date, ...) =C2=A0VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12= , $13, $14) ON CONFLICT (ID, part_date) DO NOTHING

2024-09-18 17:05= :56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:HINT: =C2=A0See server log= for query details.
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TR= ANDB:[14537]:CONTEXT: =C2=A0while inserting index tuple (88814,39) in relat= ion "PART_TAB_p2024_08_29"

2024-09-18 17:05:56 UTC:100.72.= 22.33(54582):USER1@TRANDB:[14537]:STATEMENT: =C2=A0INSERT INTO TRANDB.PART_= TAB (ID, part_date, ...) =C2=A0VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, = $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING

202= 4-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: =C2=A0= current transaction is aborted, commands ignored until end of transaction b= lock
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ST= ATEMENT: =C2=A0INSERT INTO TRANDB.EXCEP_TAB (...)
2024-09-18 17:05:56 UT= C:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR: =C2=A0current transaction= is aborted, commands ignored until end of transaction block
2024-09-18 = 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT: =C2=A0
= 2024-09-18 17:05:56 UTC:100.72.22.33(36096):USER1@TRANDB:[14551]:ERROR: =C2= =A0current transaction is aborted, commands ignored until end of transactio= n block

*********

2024-09-18 17:41:05 UTC:100.72.39.47(58778)= :USER1@TRANDB:[17456]:ERROR: =C2=A0deadlock detected
2024-09-18 17:41:05= UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:DETAIL: =C2=A0Process 17456 w= aits for ShareLock on transaction 220978890; blocked by process 17458.
P= rocess 17458 waits for ShareLock on transaction 220978889; blocked by proce= ss 17456.
Process 17456: INSERT INTO TRANDB.PART_TAB (ID, part_date, ...= ) =C2=A0VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14= ) ON CONFLICT (ID, part_date) DO NOTHING
Process 17458: INSERT INTO TRA= NDB.PART_TAB (ID, part_date, ..) =C2=A0VALUES ($1, $2, $3, $4, $5, $6, $7, = $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING 2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:HINT: =C2= =A0See server log for query details.
2024-09-18 17:41:05 UTC:100.72.39.4= 7(58778):USER1@TRANDB:[17456]:CONTEXT: =C2=A0while inserting index tuple (8= 8875,13) in relation "PART_TAB_p2024_08_29"
2024-09-18 17:41:0= 5 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:STATEMENT: =C2=A0INSERT INTO= TRANDB.PART_TAB (ID, part_date,..) =C2=A0VALUES ($1, $2, $3, $4, $5, $6, $= 7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO NOTHING =
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:LOG: = =C2=A0could not receive data from client: Connection reset by peer
2024-= 09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:LOG: =C2=A0disc= onnection: session time: 0:08:37.154 user=3DUSER1 database=3DTRANDB host=3D= XXXXX port=3D58778
--0000000000007d2b2c0622756474--