Session level variables for BGW (Was: Create partitions with default access method)

[gferrette]

Hello!

Please, is it possible to configure the partitions to be created in a default access method? For example, Postgres is configured to create tables with "heap" access method by default, so the partitions created by pg_partman has the "heap" access method. I need pg_partman to create the partitions with "columnar" access method using function "partman.create_parent()", is it possible?

Thanks in advance!

Gabriel.

[keithf4]

It looks like you can control this yourself currently by setting default_table_access_method, which is a user/session level setting you can change at anytime without any special permissions needed.

[gferrette]

Hello @keithf4 !

Thanks for replying,

The function "partman.run_maintenance" was being called by the automatic job that is scheduled hourly by parameter "pg_partman_bgw.interval". Please, do you know if is it possible that this job uses a different setting from "default_table_access_method" than the one that is configured for the intire cluster on postgresql.conf?

Thanks in advance again!

Gabriel.

[keithf4]

I don't have a means of doing that currently with the background worker. I'll change this issue into a feature request to see if something like that is possible, but I cannot provide an ETA when that may be available.

For now, you would have to call the maintenance for the table that needs the columnar access method via an external process (cron, systemd timer, etc) and set the session level variable for that call.

[gferrette]

Hello @keithf4 !

Thanks for replying! So, I'll call the maintenance externally. Thanks again!

Gabriel.

[keithf4]

So there actually is a way to do this now, but may or may not work for you.

You can currently set which user runs the partman maintenance via a GUC variable: pg_partman_bgw.role. You can alter user-level variables permanently for a given user using the ALTER ROLE command. For example (see below), my default work_mem is 4MB, but I want it to be 8MB for myself. So I alter it, and the next time I log in it is set to my new default value. You should be able to do this with the access method as well. So my recommendation would be to have this set for whichever user you have set to run the background worker maintenance.

If you have some partition sets that need heap access and some that need columnar access, that's not going to work in this case since the background worker currently runs all partition maintenance in the same state currently. You'll have to manually call the maintenance per partition set via something like cron and set the session level variable appropriately.

I'm going to have to look into if it's even possible to have the background worker run with different user/session level settings per-partition set for future development. But at least for now it seems there is a work-around.

keith@keith=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

Time: 0.564 ms
keith@keith=# alter role keith set work_mem = '8MB';
ALTER ROLE
Time: 4.099 ms

keith@keith=# \q

$ psql Expanded display is used automatically.

Current Host Server Date Time : Tue May 10 10:20:33 EDT 2022

psql (14.1) Type "help" for help.

keith@keith=# show work_mem; work_mem

8MB (1 row)

Time: 0.658 ms