We use pg_partman for highly loaded tables. On one of the table, which seems less loaded, we get a deadlock when running CALL partman.run_maintenance_proc();
ERROR: deadlock detected
CONTEXT: SQL statement "ALTER TABLE public.my_table_partitioned DETACH PARTITION public.my_table_partitioned_p2023w47"
PL/pgSQL function drop_partition_time(text,interval,boolean,boolean,text,timestamp with time zone) line 188 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 194 at assignment
SQL statement "SELECT partman.run_maintenance('public.my_table_partitioned', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE
DETAIL: Process 29798 waits for AccessExclusiveLock on relation 26433317 of database 16422; blocked by process 27697.
Process 27697 waits for RowExclusiveLock on relation 26433317 of database 16422; blocked by process 29798.
HINT: See server log for query details.
CONTEXT: PL/pgSQL function drop_partition_time(text,interval,boolean,boolean,text,timestamp with time zone) line 308 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 194 at assignment
SQL statement "SELECT partman.run_maintenance('public.my_table_partitioned', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE
DETAIL:
HINT:
CONTEXT: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 408 at RAISE
SQL statement "SELECT partman.run_maintenance('public.my_table_partitioned', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE
When I run "ALTER TABLE public.my_table_partitioned DETACH PARTITION public.my_table_partitioned_p2023w47" get the same deadlock error.
If looking at the PostgreSQL log:
2023-12-27 09:00:00 UTC:172.16.6.6(50804):user@my_db:[24497]:ERROR: deadlock detected
2023-12-27 09:00:00 UTC:172.16.6.6(50804):user@my_db:[24497]:DETAIL: Process 24497 waits for RowExclusiveLock on relation 26282571 of database 16422; blocked by process 25329.
Process 25329 waits for AccessExclusiveLock on relation 26282571 of database 16422; blocked by process 24497.
Process 24497: update my_table_partitioned set balance_amount=$1, balance_version=$2, refund_tx_id=$3, status=$4, update_time=$5, updated_by_trace_id=$6, updated_by_user_id=$7 where id=$8 and create_time=$9
Process 25329: CALL partman.run_maintenance_proc()
2023-12-27 09:00:00 UTC:172.16.6.6(50804):user@my_db:[24497]:HINT: See server log for query details.
2023-12-27 09:00:00 UTC:172.16.6.6(50804):user@my_db:[24497]:STATEMENT: update my_table_partitioned set balance_amount=$1, balance_version=$2, refund_tx_id=$3, status=$4, update_time=$5, updated_by_trace_id=$6, updated_by_user_id=$7 where id=$8 and create_time=$9
Any ideas how to address this deadlock?