run_maintenance_proc() does not create new partitions

[s3dse]

I have some monthly partitioned tables that are configured to pre-make 4 partitions. Data is inserted every day and partition maintenance is run every first day of a month. Calling run_maintenance_proc() worked for all of the tables until 1st of April. On that day for some of the tables no new partitions were created and I got the following error message:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: Child table given does not exist (<NULL>)
CONTEXT: PL/pgSQL function show_partition_info(text,text,text) line 38 at RAISE
SQL statement "SELECT child_start_time                                              FROM     partman.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table)"
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 196 at SQL statement
SQL statement "SELECT partman.run_maintenance('features2.product_clicks_with_category', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE
DETAIL: 
HINT: 
  Where: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 402 at RAISE
SQL statement "SELECT partman.run_maintenance('features2.product_clicks_with_category', p_jobmon := 't')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE

To fix the error, I set infinite_time_partitions to true, but when I call run_maintenance_proc() I still get the same error message.
Do I miss something?

I use pg_partman 4.5.1 on cloud-sql Postgres 13.

[keithf4]

Can you share the schema of your table and the part_config entry for this partition set?

[keithf4]

Can you also run \d+ for your table as well as show_partitions('schema.table'); for your table as well. Want to see what child tables actually exist vs which child tables pg_partman is able to see.

[s3dse]

Answering your questions just solved the problem:
While collecting the required information, I realised that part_config contained one entry that is not longer partitioned according to show_partitions. I removed the entry, called run_maintanence_proc() and everything worked. The other part_config entries that failed before are now working, too. The wrong entry probably also made every (alphanumerically) succeeding entry fail.
I apologise for wasting your time and thank you for your fast response.

[keithf4]

Not a problem. Glad I was able to help narrow down the issue.

[alifengineer]

Can you share the schema of your table and the part_config entry for this partition set?

insert into public.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, drop_cascade_fk, ignore_default_data)
values  ('public.payments', 'created_at', 'native', '1 mon', null, 1, 4, 30, 'none', true, null, null, true, true, false, 'YYYY_MM', 'on', true, false, false, false, '', true, 'public.template_public_payments', null, false, true, null, false, false);

show_partitions:

after run_maintenance('public.payments') partitions doesn't created

[alifengineer]

Can you share the schema of your table and the part_config entry for this partition set?

insert into public.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, drop_cascade_fk, ignore_default_data)
values  ('public.payments', 'created_at', 'native', '1 mon', null, 1, 4, 30, 'none', true, null, null, true, true, false, 'YYYY_MM', 'on', true, false, false, false, '', true, 'public.template_public_payments', null, false, true, null, false, false);

show_partitions:

after run_maintenance('public.payments') partitions don't created

Can anyone help me please ? 🙏

[keithf4]

@alifengineer unless you are seeing the same errors as this person reported, please open a new issue. Also please browse through existing closed issues for people that have reported the same problem of new partitions not being made to see if they may solve your issue

[alifengineer]

@alifengineer unless you are seeing the same errors as this person reported, please open a new issue. Also please browse through existing closed issues for people that have reported the same problem of new partitions not being made to see if they may solve your issue

Thanks, problem is solved.

I have another one question @keithf4,

Why in that case after canceling request advisory block left ? If i close this client connection and open new new connection it run without problem. Advisory block also removed.

[keithf4]

That is how advisory locks work.

https://www.postgresql.org/docs/16/explicit-locking.html#ADVISORY-LOCKS

Due to it being a procedure and not a regular function, the maintenance procedure takes out a non-transactional, session level advisory lock that must be manually closed to be run again within the same session. That manual close is at the end of the procedure which you've cancelled before it finishes. If you allow it to finish it will cleanly release the advisory lock. Non-transactional advisory locks are based on the session so if you close the session it automatically releases it.