Zombie process pile up and cannot terminate

[J T <jorge.torralba@xxxxxxxxx>]

Having an issue on Postgres 11.16 where occasionally, we start seeing a pile up of simple inserts or select just hanging and never complete. These single table insert on conflict do nothing. it gets to the point where one cannot even pg_terminate_backend. It's impossible to kill without a kill -9 and we know we don't want to do that for obvious reasons.

Having said that, I have attached a gdb backtrace for approx 10 minutes to see if anything stands out.

0x0000558c8ac625dd in hash_any ()

(gdb) bt

#0  0x0000558c8ac625dd in hash_any ()

#1  0x0000558c8b01a3ad in tag_hash ()

#2  0x0000558c8aed4cd2 in ?? ()

#3  0x0000558c8aed542e in ReadBufferExtended ()

#4  0x0000558c8ac8448a in _bt_relandgetbuf ()

#5  0x0000558c8ac88ea2 in _bt_moveright ()

#6  0x0000558c8ac89231 in _bt_search ()

#7  0x0000558c8ac89dbf in _bt_first ()

#8  0x0000558c8ac8663c in btgettuple ()

#9  0x0000558c8ac805e9 in index_getnext_tid ()

#10 0x0000558c8ac807fc in index_getnext ()

#11 0x0000558c8adcdfbe in ?? ()

#12 0x0000558c8adba7a6 in ExecScan ()

#13 0x0000558c8add1243 in ?? ()

#14 0x0000558c8adb218c in standard_ExecutorRun ()

#15 0x0000558c8ade5b99 in ?? ()

#16 0x0000558c8ade6210 in SPI_execute_snapshot ()

#17 0x0000558c8afa316e in ?? ()

#18 0x0000558c8afa513f in ?? ()

#19 0x0000558c8ad8ebdb in ?? ()

#20 0x0000558c8ad90241 in ?? ()

#21 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#22 0x0000558c8adb2428 in standard_ExecutorFinish ()

#23 0x0000558c8af05b51 in ?? ()

#24 0x0000558c8af06670 in ?? ()

#25 0x0000558c8af06a5e in PortalRun ()

#26 0x0000558c8af03ea4 in PostgresMain ()

#27 0x0000558c8ae928be in ?? ()

#28 0x0000558c8ae93758 in PostmasterMain ()

#29 0x0000558c8ac33fc4 in main ()

0x0000558c8aed2891 in ?? ()

(gdb) bt

#0  0x0000558c8aed2891 in ?? ()

#1  0x0000558c8aed4eb7 in ?? ()

#2  0x0000558c8aed542e in ReadBufferExtended ()

#3  0x0000558c8ac8448a in _bt_relandgetbuf ()

#4  0x0000558c8ac88ea2 in _bt_moveright ()

#5  0x0000558c8ac89231 in _bt_search ()

#6  0x0000558c8ac89dbf in _bt_first ()

#7  0x0000558c8ac8663c in btgettuple ()

#8  0x0000558c8ac805e9 in index_getnext_tid ()

#9  0x0000558c8ac807fc in index_getnext ()

#10 0x0000558c8adcdfbe in ?? ()

#11 0x0000558c8adba7a6 in ExecScan ()

#12 0x0000558c8add1243 in ?? ()

#13 0x0000558c8adb218c in standard_ExecutorRun ()

#14 0x0000558c8ade5b99 in ?? ()

#15 0x0000558c8ade6210 in SPI_execute_snapshot ()

#16 0x0000558c8afa316e in ?? ()

#17 0x0000558c8afa513f in ?? ()

#18 0x0000558c8ad8ebdb in ?? ()

#19 0x0000558c8ad90241 in ?? ()

#20 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#21 0x0000558c8adb2428 in standard_ExecutorFinish ()

#22 0x0000558c8af05b51 in ?? ()

#23 0x0000558c8af06670 in ?? ()

#24 0x0000558c8af06a5e in PortalRun ()

#25 0x0000558c8af03ea4 in PostgresMain ()

#26 0x0000558c8ae928be in ?? ()

#27 0x0000558c8ae93758 in PostmasterMain ()

#28 0x0000558c8ac33fc4 in main ()

0x0000558c8b0194be in hash_search_with_hash_value ()

(gdb) bt

#0  0x0000558c8b0194be in hash_search_with_hash_value ()

#1  0x0000558c8aed203e in BufTableLookup ()

#2  0x0000558c8aed4d09 in ?? ()

#3  0x0000558c8aed542e in ReadBufferExtended ()

#4  0x0000558c8ac8448a in _bt_relandgetbuf ()

#5  0x0000558c8ac88ea2 in _bt_moveright ()

#6  0x0000558c8ac89231 in _bt_search ()

#7  0x0000558c8ac89dbf in _bt_first ()

#8  0x0000558c8ac8663c in btgettuple ()

#9  0x0000558c8ac805e9 in index_getnext_tid ()

#10 0x0000558c8ac807fc in index_getnext ()

#11 0x0000558c8adcdfbe in ?? ()

#12 0x0000558c8adba7a6 in ExecScan ()

#13 0x0000558c8add1243 in ?? ()

#14 0x0000558c8adb218c in standard_ExecutorRun ()

#15 0x0000558c8ade5b99 in ?? ()

#16 0x0000558c8ade6210 in SPI_execute_snapshot ()

#17 0x0000558c8afa316e in ?? ()

#18 0x0000558c8afa513f in ?? ()

#19 0x0000558c8ad8ebdb in ?? ()

#20 0x0000558c8ad90241 in ?? ()

#21 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#22 0x0000558c8adb2428 in standard_ExecutorFinish ()

#23 0x0000558c8af05b51 in ?? ()

#24 0x0000558c8af06670 in ?? ()

#25 0x0000558c8af06a5e in PortalRun ()

#26 0x0000558c8af03ea4 in PostgresMain ()

#27 0x0000558c8ae928be in ?? ()

#28 0x0000558c8ae93758 in PostmasterMain ()

#29 0x0000558c8ac33fc4 in main ()

0x0000558c8ac840a0 in _bt_checkpage ()

(gdb) bt

#0  0x0000558c8ac840a0 in _bt_checkpage ()

#1  0x0000558c8ac844a2 in _bt_relandgetbuf ()

#2  0x0000558c8ac88ea2 in _bt_moveright ()

#3  0x0000558c8ac89231 in _bt_search ()

#4  0x0000558c8ac89dbf in _bt_first ()

#5  0x0000558c8ac8663c in btgettuple ()

#6  0x0000558c8ac805e9 in index_getnext_tid ()

#7  0x0000558c8ac807fc in index_getnext ()

#8  0x0000558c8adcdfbe in ?? ()

#9  0x0000558c8adba7a6 in ExecScan ()

#10 0x0000558c8add1243 in ?? ()

#11 0x0000558c8adb218c in standard_ExecutorRun ()

#12 0x0000558c8ade5b99 in ?? ()

#13 0x0000558c8ade6210 in SPI_execute_snapshot ()

#14 0x0000558c8afa316e in ?? ()

#15 0x0000558c8afa513f in ?? ()

#16 0x0000558c8ad8ebdb in ?? ()

#17 0x0000558c8ad90241 in ?? ()

#18 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#19 0x0000558c8adb2428 in standard_ExecutorFinish ()

#20 0x0000558c8af05b51 in ?? ()

#21 0x0000558c8af06670 in ?? ()

#22 0x0000558c8af06a5e in PortalRun ()

#23 0x0000558c8af03ea4 in PostgresMain ()

#24 0x0000558c8ae928be in ?? ()

#25 0x0000558c8ae93758 in PostmasterMain ()

#26 0x0000558c8ac33fc4 in main ()

0x0000558c8afe3a45 in ?? ()

(gdb) bt

#0  0x0000558c8afe3a45 in ?? ()

#1  0x0000558c8afe5ec8 in bttextcmp ()

#2  0x0000558c8b014724 in FunctionCall2Coll ()

#3  0x0000558c8ac88ce5 in _bt_compare ()

#4  0x0000558c8ac88e62 in _bt_moveright ()

#5  0x0000558c8ac89231 in _bt_search ()

#6  0x0000558c8ac89dbf in _bt_first ()

#7  0x0000558c8ac8663c in btgettuple ()

#8  0x0000558c8ac805e9 in index_getnext_tid ()

#9  0x0000558c8ac807fc in index_getnext ()

#10 0x0000558c8adcdfbe in ?? ()

#11 0x0000558c8adba7a6 in ExecScan ()

#12 0x0000558c8add1243 in ?? ()

#13 0x0000558c8adb218c in standard_ExecutorRun ()

#14 0x0000558c8ade5b99 in ?? ()

#15 0x0000558c8ade6210 in SPI_execute_snapshot ()

#16 0x0000558c8afa316e in ?? ()

#17 0x0000558c8afa513f in ?? ()

#18 0x0000558c8ad8ebdb in ?? ()

#19 0x0000558c8ad90241 in ?? ()

#20 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#21 0x0000558c8adb2428 in standard_ExecutorFinish ()

#22 0x0000558c8af05b51 in ?? ()

#23 0x0000558c8af06670 in ?? ()

#24 0x0000558c8af06a5e in PortalRun ()

#25 0x0000558c8af03ea4 in PostgresMain ()

#26 0x0000558c8ae928be in ?? ()

#27 0x0000558c8ae93758 in PostmasterMain ()

#28 0x0000558c8ac33fc4 in main ()

0x0000558c8ac88e30 in _bt_moveright ()

(gdb) bt

#0  0x0000558c8ac88e30 in _bt_moveright ()

#1  0x0000558c8ac89231 in _bt_search ()

#2  0x0000558c8ac89dbf in _bt_first ()

#3  0x0000558c8ac8663c in btgettuple ()

#4  0x0000558c8ac805e9 in index_getnext_tid ()

#5  0x0000558c8ac807fc in index_getnext ()

#6  0x0000558c8adcdfbe in ?? ()

#7  0x0000558c8adba7a6 in ExecScan ()

#8  0x0000558c8add1243 in ?? ()

#9  0x0000558c8adb218c in standard_ExecutorRun ()

#10 0x0000558c8ade5b99 in ?? ()

#11 0x0000558c8ade6210 in SPI_execute_snapshot ()

#12 0x0000558c8afa316e in ?? ()

#13 0x0000558c8afa513f in ?? ()

#14 0x0000558c8ad8ebdb in ?? ()

#15 0x0000558c8ad90241 in ?? ()

#16 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#17 0x0000558c8adb2428 in standard_ExecutorFinish ()

#18 0x0000558c8af05b51 in ?? ()

#19 0x0000558c8af06670 in ?? ()

#20 0x0000558c8af06a5e in PortalRun ()

#21 0x0000558c8af03ea4 in PostgresMain ()

#22 0x0000558c8ae928be in ?? ()

#23 0x0000558c8ae93758 in PostmasterMain ()

#24 0x0000558c8ac33fc4 in main ()

0x0000558c8aef4fca in LWLockRelease ()

(gdb) bt

#0  0x0000558c8aef4fca in LWLockRelease ()

#1  0x0000558c8aed4ec2 in ?? ()

#2  0x0000558c8aed542e in ReadBufferExtended ()

#3  0x0000558c8ac8448a in _bt_relandgetbuf ()

#4  0x0000558c8ac88ea2 in _bt_moveright ()

#5  0x0000558c8ac89231 in _bt_search ()

#6  0x0000558c8ac89dbf in _bt_first ()

#7  0x0000558c8ac8663c in btgettuple ()

#8  0x0000558c8ac805e9 in index_getnext_tid ()

#9  0x0000558c8ac807fc in index_getnext ()

#10 0x0000558c8adcdfbe in ?? ()

#11 0x0000558c8adba7a6 in ExecScan ()

#12 0x0000558c8add1243 in ?? ()

#13 0x0000558c8adb218c in standard_ExecutorRun ()

#14 0x0000558c8ade5b99 in ?? ()

#15 0x0000558c8ade6210 in SPI_execute_snapshot ()

#16 0x0000558c8afa316e in ?? ()

#17 0x0000558c8afa513f in ?? ()

#18 0x0000558c8ad8ebdb in ?? ()

#19 0x0000558c8ad90241 in ?? ()

#20 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#21 0x0000558c8adb2428 in standard_ExecutorFinish ()

#22 0x0000558c8af05b51 in ?? ()

#23 0x0000558c8af06670 in ?? ()

#24 0x0000558c8af06a5e in PortalRun ()

#25 0x0000558c8af03ea4 in PostgresMain ()

#26 0x0000558c8ae928be in ?? ()

#27 0x0000558c8ae93758 in PostmasterMain ()

#28 0x0000558c8ac33fc4 in main ()

0x0000558c8aef3a46 in ?? ()

(gdb) bt

#0  0x0000558c8aef3a46 in ?? ()

#1  0x0000558c8aef4685 in LWLockAcquire ()

#2  0x0000558c8ac84497 in _bt_relandgetbuf ()

#3  0x0000558c8ac88ea2 in _bt_moveright ()

#4  0x0000558c8ac89231 in _bt_search ()

#5  0x0000558c8ac89dbf in _bt_first ()

#6  0x0000558c8ac8663c in btgettuple ()

#7  0x0000558c8ac805e9 in index_getnext_tid ()

#8  0x0000558c8ac807fc in index_getnext ()

#9  0x0000558c8adcdfbe in ?? ()

#10 0x0000558c8adba7a6 in ExecScan ()

#11 0x0000558c8add1243 in ?? ()

#12 0x0000558c8adb218c in standard_ExecutorRun ()

#13 0x0000558c8ade5b99 in ?? ()

#14 0x0000558c8ade6210 in SPI_execute_snapshot ()

#15 0x0000558c8afa316e in ?? ()

#16 0x0000558c8afa513f in ?? ()

#17 0x0000558c8ad8ebdb in ?? ()

#18 0x0000558c8ad90241 in ?? ()

#19 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#20 0x0000558c8adb2428 in standard_ExecutorFinish ()

#21 0x0000558c8af05b51 in ?? ()

#22 0x0000558c8af06670 in ?? ()

#23 0x0000558c8af06a5e in PortalRun ()

#24 0x0000558c8af03ea4 in PostgresMain ()

#25 0x0000558c8ae928be in ?? ()

#26 0x0000558c8ae93758 in PostmasterMain ()

#27 0x0000558c8ac33fc4 in main ()

0x0000558c8b01947c in hash_search_with_hash_value ()

(gdb) bt

#0  0x0000558c8b01947c in hash_search_with_hash_value ()

#1  0x0000558c8aed203e in BufTableLookup ()

#2  0x0000558c8aed4d09 in ?? ()

#3  0x0000558c8aed542e in ReadBufferExtended ()

#4  0x0000558c8ac8448a in _bt_relandgetbuf ()

#5  0x0000558c8ac88ea2 in _bt_moveright ()

#6  0x0000558c8ac89231 in _bt_search ()

#7  0x0000558c8ac89dbf in _bt_first ()

#8  0x0000558c8ac8663c in btgettuple ()

#9  0x0000558c8ac805e9 in index_getnext_tid ()

#10 0x0000558c8ac807fc in index_getnext ()

#11 0x0000558c8adcdfbe in ?? ()

#12 0x0000558c8adba7a6 in ExecScan ()

#13 0x0000558c8add1243 in ?? ()

#14 0x0000558c8adb218c in standard_ExecutorRun ()

#15 0x0000558c8ade5b99 in ?? ()

#16 0x0000558c8ade6210 in SPI_execute_snapshot ()

#17 0x0000558c8afa316e in ?? ()

#18 0x0000558c8afa513f in ?? ()

#19 0x0000558c8ad8ebdb in ?? ()

#20 0x0000558c8ad90241 in ?? ()

#21 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#22 0x0000558c8adb2428 in standard_ExecutorFinish ()

#23 0x0000558c8af05b51 in ?? ()

#24 0x0000558c8af06670 in ?? ()

#25 0x0000558c8af06a5e in PortalRun ()

#26 0x0000558c8af03ea4 in PostgresMain ()

#27 0x0000558c8ae928be in ?? ()

#28 0x0000558c8ae93758 in PostmasterMain ()

#29 0x0000558c8ac33fc4 in main ()

0x0000558c8ac88e30 in _bt_moveright ()

(gdb) bt

#0  0x0000558c8ac88e30 in _bt_moveright ()

#1  0x0000558c8ac89231 in _bt_search ()

#2  0x0000558c8ac89dbf in _bt_first ()

#3  0x0000558c8ac8663c in btgettuple ()

#4  0x0000558c8ac805e9 in index_getnext_tid ()

#5  0x0000558c8ac807fc in index_getnext ()

#6  0x0000558c8adcdfbe in ?? ()

#7  0x0000558c8adba7a6 in ExecScan ()

#8  0x0000558c8add1243 in ?? ()

#9  0x0000558c8adb218c in standard_ExecutorRun ()

#10 0x0000558c8ade5b99 in ?? ()

#11 0x0000558c8ade6210 in SPI_execute_snapshot ()

#12 0x0000558c8afa316e in ?? ()

#13 0x0000558c8afa513f in ?? ()

#14 0x0000558c8ad8ebdb in ?? ()

#15 0x0000558c8ad90241 in ?? ()

#16 0x0000558c8ad954b8 in AfterTriggerEndQuery ()

#17 0x0000558c8adb2428 in standard_ExecutorFinish ()

#18 0x0000558c8af05b51 in ?? ()

#19 0x0000558c8af06670 in ?? ()

#20 0x0000558c8af06a5e in PortalRun ()

#21 0x0000558c8af03ea4 in PostgresMain ()

#22 0x0000558c8ae928be in ?? ()

#23 0x0000558c8ae93758 in PostmasterMain ()

#24 0x0000558c8ac33fc4 in main ()

C

--

Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.