On Thu, 19 Sept 2024 at 03:02, Adrian Klaver <adrian.kla...@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
