It should be possible to specify tablespace for table partitions

[OlegUfaev]

The current pg_partman implementation (5.0.0) does not support specifying tablespace for table partitions and also ignores tablespace specified for parent/template tables. As a result, partitions always fall into the default tablespace. I believe this is not expected behavior.

It should be possible:

• to explicitly specify which tablespace to use for table partitinons by passing its name to the create_parent function
• or pg_partman should dynamically retrieve the current tablespace that is specified for the parent/template tables and use it for table partitions.

Most likely using the tablespace of the parent table would be the most expected behavior.

[keithf4]

Hmm... as of PostgresSQL 12, it should be getting the tablespace from the parent table. This had been a template table feature before that, but was fixed to just let PG handle this natively in version 4.3. Nothing should have changed for that in 5.0 as far as I know. Is it working in a prior version for you?

#274

So you should be able to set the tablespace you want on the parent table before you run create_parent() against it.

Can you provide an example that shows tablespace inheritance not working?

[OlegUfaev]

@keithf4 you're right, PostgreSQL 12+ gets the tablespace from the parent table, but only if we use CREATE TABLE ... PARTITION OF .. command and directly mention parent table in that command.

pg_partman, on the other hand, creates partition in two steps:

1. pg_partman creates new table based on the template table using the command:
   CREATE TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS INCLUDING GENERATED)
   That new table will be created in default tablespace, because the create command does not have the TABLESPACE option.
2. pg_partman attaches new table as partition to the parent table.

The correct command to create a new table (which pg_partman will attach later as partition) should be:
CREATE TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS INCLUDING GENERATED) TABLESPACE %I

And this is how we can get the tablespace name of the parent table:
SELECT tablespace INTO v_parent_tablespace FROM pg_tables WHERE tablename = v_parent_tablename and schemaname = v_parent_schema;

[OlegUfaev]

Example

CREATE TABLE public.my_table (
  date timestamptz NOT NULL
) PARTITION BY RANGE (date)
TABLESPACE my_tablespace;

SELECT partman.create_parent(
    p_parent_table            => 'public.my_table'
    , p_control               => 'date'
    , p_interval              => '1 day'
    , p_epoch                 => 'none'
    , p_type                  => 'range'
    , p_premake               => 4
    , p_default_table         => true
    , p_automatic_maintenance => 'on'
    , p_template_table        => 'public.my_table'
    , p_jobmon                => false
);

After execution you can see few partitions like:

my_table_default
my_table_p20240107
...
my_table_p20240115

Current result: TABLESPACE pg_default is specified for all partitions
Expected result: TABLESPACE my_tablespace is specified for all partitions

Environment information

SELECT version();
-- output:  PostgreSQL 15.4 (Ubuntu 15.4-2.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

SELECT extname, extversion from pg_extension where extname = 'pg_partman';
-- output: pg_partman 5.0.0

[OlegUfaev]

@keithf4

This had been a template table feature before that, but was fixed to just let PG handle this natively in version 4.3. Nothing should have changed for that in 5.0 as far as I know. Is it working in a prior version for you?

I can't say, because we started using pg_partman since version 5.0.0.

[keithf4]

Thanks for finding that! I'll try and work on a fix for the next release

[keithf4]

So looks like I was handling this differently in versions older than 5.x and tablespace inheritance was working then. Looks like I missed carrying that method forward into 5.0. So will have a fix for this in 5.1 but thankfully don't need to backpatch this for 4.x.

[keithf4]

If you're able to test, I do have a beta PR up with a fix for the tablespace issue if you want to give it a try

#615

[OlegUfaev]

@keithf4, I tried applying the updates/pg_partman--5.0.1--5.1.0-beta.sql scripts and found several syntax errors.

1. create_partition_id

The following error was generated

ERROR:  syntax error at or near "'i'"
LINE 284:                 WHEN 'i' THEN v_replident_string := format('...
                               ^ 

I corrected it this way (added a semicolon)

            CASE v_parent_replident
                WHEN 'f' THEN v_replident_string := 'FULL';
                WHEN 'i' THEN v_replident_string := format('USING INDEX %I', v_parent_replident_index);
                WHEN 'n' THEN v_replident_string := 'NOTHING';
            ELSE
                RAISE EXCEPTION 'create_partition: Unknown replication identity encountered. Please report as a bug on pg_partman''s github';
            END CASE;

2. create_partition_time

Same error as above, same fix

            CASE v_parent_replident
                WHEN 'f' THEN v_replident_string := 'FULL';
                WHEN 'i' THEN v_replident_string := format('USING INDEX %I', v_parent_replident_index);
                WHEN 'n' THEN v_replident_string := 'NOTHING';
            ELSE
                RAISE EXCEPTION 'create_partition: Unknown replication identity encountered. Please report as a bug on pg_partman''s github';
            END CASE;

3. dump_partitioned_table_definition

        pc.default_table,
        pc.maintenance_order
        pc.retention_keep_publication
    INTO
        v_parent_table,
        v_control,
        v_partition_type,

The following error was generated

ERROR:  syntax error at or near "."
LINE 63:         pc.retention_keep_publication
                   ^ 

I corrected it this way (added a comma after pc.maintenance_order)

        pc.default_table,
        pc.maintenance_order,
        pc.retention_keep_publication
    INTO
        v_parent_table,
        v_control,
        v_partition_type,

Everything else updated with no problems.

[OlegUfaev]

After that, I re-executed the script with the test table

CREATE TABLE public.my_table (
  date timestamptz NOT NULL
) PARTITION BY RANGE (date)
TABLESPACE my_tablespace;

SELECT partman.create_parent(
    p_parent_table            => 'public.my_table'
    , p_control               => 'date'
    , p_interval              => '1 day'
    , p_epoch                 => 'none'
    , p_type                  => 'range'
    , p_premake               => 4
    , p_default_table         => true
    , p_automatic_maintenance => 'on'
    , p_template_table        => 'public.my_table'
    , p_jobmon                => false
);

After execution I saw few partitions, as expected:

my_table_default
my_table_p20240126
my_table_p20240127
...
my_table_p20240203

For partitions my_table_p20240126, my_table_p20240127, ..., my_table_p20240203 - tablespace my_tablespace is specified, as expected.

BUT for my_table_default partition is still specified pg_default tablespace, which is NOT expected.

[keithf4]

I hadn't had time to get to writing a unit test for this one yet, so apologies for the syntax errors. Thank you for testing and the corrections! I'll get an update pushed up when I have a moment

[OlegUfaev]

No worries, happy to help.

BTW, I tested changing the behavior of infinite_time_partitions = true on empty tables, works as expected and creates as many partitions ahead as specified in premake.

[keithf4]

I've pushed an update to the 5.1.0 branch that should fix the missing comma and also properly set the tablespace on the default table. Thanks again!

[OlegUfaev]

BUT for my_table_default partition is still specified pg_default tablespace, which is NOT expected.

@keithf4 , I've just checked on the 5.1.0-beta branch, now all partitions have the expected tablespace. I'm looking forward to the 5.1 release )

[OlegUfaev]

BTW, I had trouble running the update scripts again

1. create_partition_id

The following error was generated

ERROR:  syntax error at or near "'i'"
LINE 284:                 WHEN 'i' THEN v_replident_string := format('...
                               ^ 

I corrected it this way (added a semicolon)

            CASE v_parent_replident
                WHEN 'f' THEN v_replident_string := 'FULL';
                WHEN 'i' THEN v_replident_string := format('USING INDEX %I', v_parent_replident_index);
                WHEN 'n' THEN v_replident_string := 'NOTHING';
            ELSE
                RAISE EXCEPTION 'create_partition: Unknown replication identity encountered. Please report as a bug on pg_partman''s github';
            END CASE;

2. create_partition_time

Same error as above, same fix

            CASE v_parent_replident
                WHEN 'f' THEN v_replident_string := 'FULL';
                WHEN 'i' THEN v_replident_string := format('USING INDEX %I', v_parent_replident_index);
                WHEN 'n' THEN v_replident_string := 'NOTHING';
            ELSE
                RAISE EXCEPTION 'create_partition: Unknown replication identity encountered. Please report as a bug on pg_partman''s github';
            END CASE;

[keithf4]

Thanks again!

[keithf4]

I've updated the PR with your suggested fixes and I also added some unit tests for this feature as well. Will hopefully be able to do the final release in the near future

[OlegUfaev]

@keithf4, I just checked the upgrade script pg_partman--5.0.1--5.1.0-beta.sql, everything went without errors, but there is one critical problem with the inherit_replica_identity function.

There is no schema declaration in the creation instructions, so this function is created in the public schema.

Here's how the function is created now

CREATE FUNCTION inherit_replica_identity (p_parent_schemaname text, p_parent_tablename text, p_child_tablename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
...

And here's how the function should be created (with @extschema@)

CREATE FUNCTION @extschema@.inherit_replica_identity (p_parent_schemaname text, p_parent_tablename text, p_child_tablename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
...

[keithf4]

Thanks for letting me know!

[keithf4]

And here's how the function should be created (with @extschema@)

CREATE FUNCTION @extschema@.inherit_replica_identity (p_parent_schemaname text, p_parent_tablename text, p_child_tablename text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
...

This has been fixed

[OlegUfaev]

Just checked again - works as expected! Thank you!

I'm looking forward to the 5.1 release )

[keithf4]

Version 5.1 has been released. Thank you again for all your help with testing!