Re: IO related waits

On Thu, 19 Sept 2024 at 03:02, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(at)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(at)TRANDB:[14537]:HINT: See
server log for query details.
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1(at)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(at)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(at)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(at)TRANDB:[14537]:STATEMENT:
INSERT INTO TRANDB.EXCEP_TAB (...)
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1(at)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(at)TRANDB:[14537]:STATEMENT:

2024-09-18 17:05:56 UTC:100.72.22.33(36096):USER1(at)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(at)TRANDB:[17456]:ERROR:
deadlock detected
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1(at)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(at)TRANDB:[17456]:HINT: See
server log for query details.
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1(at)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(at)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(at)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(at)TRANDB:[17456]:LOG:
disconnection: session time: 0:08:37.154 user=USER1 database=TRANDB
host=XXXXX port=58778