Partition naming using the set_init_callback()

[megadawn]

Hi,
Sorry for stupid question it's not clear for me how can I rename a partition in the handler part_init_callback() during the partition creation, so that when I call create_range_partitions() it would make names like table_2016_11_01, table_2016_11_02 etc. Could you show some example please?

cheers

[funbringer]

@zilder, could you please come up with an example?

[megadawn]

anyone?

[zilder]

Hi @megadawn,

Actually it is a good question and it's not that as easy as it seems to be. The main reason is that you cannot just rename partition and expect that everything will work. pg_pathman uses certain rules to generate names for check constraints which are depend on partition name. If you rename partition and don't rename check constraint associated with it then pg_pathman won't be able to locate it. So you need to rename both partition and it's check constraint.

But then when I came up with a callback function that renames both I've discovered an issue that doesn't make it possible to use this callback with auto partition creation. We will fix this in next release which will be available this or next week.

[megadawn]

would it be easier to make another parameter in create_partitions function which let user choose to name tables according to chosen key column data type and interval?

[funbringer]

@megadawn, we decided to make it as simple as possible. We're going to intercept the renaming operation and generate a new name for pg_pathman's check constraint too. This feature will be available in 1.2.

[zilder]

Hi @megadawn,

I've fixed the problem I mentioned above. So now you can create a callback function to rename your partitions. Could you please try it? This fix is available in callback_fix branch, you need to rebuild it and recreate an extension. For example,

create or replace function test_callback(param jsonb)
returns void as
$$
declare
    range_min timestamp;
    new_relname text;
begin
    range_min := (param->>'range_min')::timestamp;

    -- generate new name for partition
    new_relname := format('%s_%s',
        param->>'parent',
        to_char(range_min, 'YYYY_MM_DD'));

    -- rename partition
    execute format('alter table %s rename to %s',
        param->>'partition',
        new_relname);
end
$$
language plpgsql;

And here is an example of how you can use it:

create table test(dt timestamp not null, b int);
insert into test select generate_series('2016-01-01'::date, '2016-02-28'::date, '1 day'::interval);
select set_init_callback('test', 'test_callback');
select create_range_partitions('test', 'dt', '2016-01-01'::date, '1 month'::interval);
insert into test values ('2016-03-15');
\d+ test
                                   Table "public.test"
 Column |            Type             | Modifiers | Storage | Stats target | Description 
--------+-----------------------------+-----------+---------+--------------+-------------
 dt     | timestamp without time zone | not null  | plain   |              | 
 b      | integer                     |           | plain   |              | 
Child tables: test_2016_01_01,
              test_2016_02_01,
              test_2016_03_01

would it be easier to make another parameter in create_partitions function which let user choose to name tables according to chosen key column data type and interval?

We need to put more thought in it. Probably we'll implement it in some future release.

[zilder]

This fix is now available in master branch and new 1.2 release