partman.run_maintenance() error on Subscriber

[rsquaretrade]

Hello. I am testing migrating a table to native partitioning, this table x is also replicating to the warehouse. I configured partman.part_config and is working, I've been testing partman.run_maintenance. My problem is on the Subscriber, when I SELECT partman.run_maintenance('x') i get this error:

postgresql://postgres@[local:/var/run/postgresql/14/warehouse_staging]:5432 17:17:04 warehouse=> SELECT partman.run_maintenance(); ERROR: ALTER SUBSCRIPTION ... REFRESH cannot run inside a transaction block CONTEXT: SQL statement "ALTER SUBSCRIPTION x_sub REFRESH PUBLICATION" PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 379 at EXECUTE DETAIL: HINT: CONTEXT: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 413 at RAISE

I noticed that if I put subscription_refresh=NULL instead of subscription_refresh=x_sub, run_maintenance() runs fine without the error, but it also does not "ALTER SUBSCRIPTION x_sub REFRESH PUBLICATION" therefore the tables on the Subscriber is not updated unless I manually run the above sql. What am I doing wrong?

[keithf4]

What version of PostgreSQL are you running?

[rsquaretrade]

14.8 on both Publisher and Subscriber.

Also, I ran a separate test changing the interval from MONTHLY to DAILY (premake=1). Today, Sunday, the child table in the Publisher showed up but NOT in the Subscriber. No errors in the log.

This is what's in part_config in the Subscriber, could it be a configuration issue?

UPDATE partman.part_config SET subscription_refresh='x_sub_dailytest' WHERE parent_table='dba.x_daily' AND template_table='partman.template_x_daily';

parent_table │ dba.x_daily control │ created partition_type │ native partition_interval │ 1 day constraint_cols │ premake │ 1 optimize_trigger │ 4 optimize_constraint │ 30 epoch │ none inherit_fk │ t retention │ 4 months retention_schema │ x_archive retention_keep_table │ f retention_keep_index │ f infinite_time_partitions │ t datetime_string │ YYYY_MM_DD automatic_maintenance │ on jobmon │ f sub_partition_set_full │ f undo_in_progress │ f trigger_exception_handling │ f upsert │ trigger_return_null │ t template_table │ partman.template_dba_x_daily publications │ inherit_privileges │ t constraint_valid │ t subscription_refresh │ x_sub_dailytest drop_cascade_fk │ f ignore_default_data │ f

[keithf4]

So this looks to be a change in PG14. Prior to that, REFRESH SUBSCRIPTION could be called within a function which calls things within a transaction block. There's nothing explicit in the PG14 release notes that says this, but there were extensive changes to logical replication in 14 that allowed streaming of in-process transactions, so may be related to that

https://www.postgresql.org/docs/14/release-14.html Section E.10.3.2.1

Also, this line in the ALTER SUBSCRIPTION docs did not exist prior to 14 (https://www.postgresql.org/docs/14/sql-altersubscription.html)

Commands ALTER SUBSCRIPTION ... REFRESH PUBLICATION and ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ... with refresh option as true cannot be executed inside a transaction block.

So, unfortunately for now, I'm going to have to drop this feature from pg_partman. I'll try and followup with core devs and see if there's anything that can be done here, but seems like you'll have to schedule an independent call to refresh the subscriptions for partitioned tables for now.

[rsquaretrade]

Thank you, I appreciate your timely response!

[keithf4]

Thanks for reporting the issue!