Deadlock during run_maintenance_proc

[SarkanMo-tomtom]

Hey everybody! I have pg_partman on my cosmos db for PostgreSQL (16) to partition my table called transactions, in my table my primary key is uuidv7 which I use it as my partition key, the table is under load and all requests are inserts to the table, id generated by current time so all inserts are for right now I don't have inserts for past or future. here you can see my partman config:

Note: I need constraint to be valid at least in the range of 30 days partitions, and also I need to drop tables to free up space on my disk.

select * from partman.part_config;
-[ RECORD 1 ]--------------+---------------------------------------
parent_table               | public.transactions
control                    | id
time_encoder               | partman.uuid7_time_encoder
time_decoder               | partman.uuid7_time_decoder
partition_interval         | 1 day
partition_type             | range
premake                    | 7
automatic_maintenance      | on
template_table             | public.template_transactions_partition
retention                  | 6 months
retention_schema           |
retention_keep_index       | t
retention_keep_table       | f
epoch                      | none
constraint_cols            | {}
optimize_constraint        | 30
infinite_time_partitions   | f
datetime_string            | YYYYMMDD
jobmon                     | t
sub_partition_set_full     | f
undo_in_progress           | f
inherit_privileges         | f
constraint_valid           | t
ignore_default_data        | t
date_trunc_interval        |
maintenance_order          |
retention_keep_publication | f
maintenance_last_run       | 2025-12-10 11:14:15.016959+00

after setting retention for my table I'm getting deadlock from run_maintenance_proc() which happens during dropping old partition

CALL partman.run_maintenance_proc();
ERROR:  deadlock detected
CONTEXT: SQL statement "DROP TABLE public.transactions_p20250610"
PL/pgSQL function drop_partition_time(text,interval,boolean,boolean,text,timestamp with time zone) line 250 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 336 at assignment
SQL statement "SELECT partman.run_maintenance('public.transactions', p_jobmon := 't', p_analyze := 'f')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE
DETAIL: Process 2313753 waits for AccessExclusiveLock on relation 3754592 of database 16388; blocked by process 2309514.
Process 2309514 waits for RowExclusiveLock on relation 3754581 of database 16388; blocked by process 2313753.
HINT: See server log for query details.
CONTEXT: PL/pgSQL function drop_partition_time(text,interval,boolean,boolean,text,timestamp with time zone) line 307 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 336 at assignment
SQL statement "SELECT partman.run_maintenance('public.transactions', p_jobmon := 't', p_analyze := 'f')"
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 474 at RAISE
SQL statement "SELECT partman.run_maintenance('public.transactions', p_jobmon := 't', p_analyze := 'f')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE

may you please help me how can I resolve this problem?

[keithf4]

Can you try making a more detailed log_line_prefix that includes at least timestamps, PIDs, log line numbers, and users? Here's an example of one that I frequently use

log_line_prefix = '%m [%r] [%p]: [l-%l] user=%u,db=%d,app=%a,e=%e '

This will allow you to see when things are happening, the PID of the action for the given log line, who did it and a line number to easily keep track of the order of operations for actions by the same PID (in case timestamps are the same).

Once you have that set, you should be able to look back through your logs and look up both queries that were running for the PIDs that are involved in the deadlock. There's no guarantee of order for a deadlocked session so it may take some hunting around in the logs to find everything involved. The log line number helps to keep things in the order they happened when extracting them for study.

Another user ran into this similar issue here in this issue with only inserts happening: #796

From what we could tell there, it was the locks that PostgreSQL itself takes out around the default table during maintenance due to having to change its constraints whenever a new table is dropped/added. For him, I'd suggested trying to take out an exclusive lock on the partition set before maintenance ran on it to see if that might help. I'd avoided doing that in pg_partman itself since that's a pretty heavyweight lock to be doing by default on normal maintenance runs and it may not even solve the problem.

You can also try taking this particular partition set out of the full maintenance run that tries to run on all active partition sets to reduce the length of the transaction involved. Instructions for that are in that issue as well.

[keithf4]

Actually, since you said all of your inserts are for "now" you might be able to drop the default table. Note that would cause errors to be thrown and that data would be lost if its outside the expected child table window, but if that's ok for your situation, then that may solve the issue if yours is the same as the other user's.

[SarkanMo-tomtom]

I check there were a huge amount of data for missing partitions I manually moved all of them to their corresponding partitions and currently I don't have anything in my default, but on the other hand removing default partition leave me a bigger problem if for any reason the child partition is not there I will loss data which is not acceptable in our setup. I'm trying to gather logs once I got enough logs I will share with you.

[keithf4]

It doesn't matter if there is data in the default or not. In the case of the other user, it was simply the locks that PG is taking on the default and parent to drop the old table. Either dropping or creating a new table requires a change to the constraint that exists on the default.

If that is the case, it's not really anything in pg_partman itself causing the deadlock and you may have to escalate to creating a bug with upstream if you have a reproducible test case.