Re: IO related waits

public inbox for [email protected]  
help / color / mirror / Atom feed

From: veem v <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: Christophe Pettus <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: IO related waits
Date: Thu, 19 Sep 2024 14:47:21 +0530
Message-ID: <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
	<[email protected]>
	<CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
	<[email protected]>
	<CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
	<[email protected]>
	<CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
	<CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
	<[email protected]>

On Thu, 19 Sept 2024 at 03:02, Adrian Klaver <[email protected]>
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

view thread (17+ 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], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: IO related waits
  In-Reply-To: <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>

* 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