Re: IO related waits - Mailing list pgsql-general
| From | veem v |
|---|---|
| Subject | Re: IO related waits |
| Date | |
| Msg-id | CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com Whole thread Raw |
| In response to | Re: IO related waits (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: IO related waits
|
| List | pgsql-general |
On Thu, 19 Sept 2024 at 03:02, Adrian Klaver <adrian.klaver@aklaver.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@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
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
pgsql-general by date: