run_maintenance continuously creates partitions

[CharlesLiu02]

Hi, I have a partitioned table setup with a cron job that calls partman.run_maintenance('table') every week. About 3 weeks ago, my job started to fail due to timeouts. I just noticed this week and I was investigating why the job failed. It seems like partman is trying to create an infinite amount of partitions when running maintenance. I'm not entirely sure why.

Here's my part_config:

|parent_table|control|partition_type|partition_interval|constraint_cols|premake|optimize_trigger|optimize_constraint|epoch|inherit_fk|retention|retention_schema|retention_keep_table|retention_keep_index|infinite_time_partitions|datetime_string|automatic_maintenance|jobmon|sub_partition_set_full|undo_in_progress|trigger_exception_handling|upsert|trigger_return_null|template_table|publications|inherit_privileges|constraint_valid|subscription_refresh|
|------------|-------|--------------|------------------|---------------|-------|----------------|-------------------|-----|----------|---------|----------------|--------------------|--------------------|------------------------|---------------|---------------------|------|----------------------|----------------|--------------------------|------|-------------------|--------------|------------|------------------|----------------|--------------------|
|table|timestamp|native|7 days||4|4|30|none|true|1 year||false|false|true|IYYY"w"IW|on|true|false|false|false||true|partman.template_table||false|true||

The error changes when I run. I've seen this error:

SQL Error [57014]: ERROR: canceling statement due to statement timeout
  Where: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 223 at IF
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 273 at assignment

I've also seen this:

SQL Error [57014]: ERROR: canceling statement due to statement timeout
  Where: SQL statement "ALTER TABLE table ATTACH PARTITION table_p2063w03 FOR VALUES FROM ('2063-01-15 00:00:00-08') TO ('2063-01-22 00:00:00-08')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 239 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 273 at assignment

When I remove the statement timeout, it eventually fails with this error message:

SQL Error [P0001]: ERROR: out of shared memory
CONTEXT: SQL statement "ALTER TABLE table ATTACH PARTITION table_p2312w08 FOR VALUES FROM ('2312-02-19 00:00:00-08') TO ('2312-02-26 00:00:00-08')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 239 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 273 at assignment
DETAIL: 
HINT: You might need to increase max_locks_per_transaction.
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 479 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 273 at assignment
DETAIL: 
HINT: 
  Where: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 402 at RAISE

[keithf4]

What version of pg_partman?

Do you have any data in the default partition?

[CharlesLiu02]

Thanks for the reply! Turns out there was data in the default partition. Not entirely sure how they ended up in the default since there was a partition created for that week. After moving the data, run_maintenance worked fine. Thanks!