Migrating existing partitions to pg_pathman

[vmihailenco]

Assuming that I already use table inheritance to partition my table what I need to do to migrate to pg_pathman? My experiments show that I only need to execute

select create_partitions_from_range('notices', 'id', 0::bigint, -1::bigint, 22468047667200000);

to start using pg_pathman. Is that correct?

Please note that I had to use start_value=0 and end_value=-1 to prevent pg_statsman from creating any partitions... It is totally fine, but probably should be documented.

[funbringer]

The upcoming release will contain a function to add a parent table to pathman_config. It will perform certain checks to ensure that the table is integrated seamlessly, otherwise an error will be emitted.

[zilder]

This wasn't documented because we didn't expect that someone will use it that way : ) We've been already told that it would be a useful feature to enable pg_pathman for existing partitioning. There is one main problem -- we should validate this existing setup to be sure that check constraints satisfy the certain format (e.g. for range partitioning it must be attr >= const and attr < const). If it's not then pg_pathman won't be able to use them to filter partitions.

[funbringer]

This function (add_to_pathman_config()) has been implemented in a6e4260. The upcoming release is going to provide it.

[vmihailenco]

Thanks - going to test it.

It is a bit strange that function has general name add_to_pathman_config, but expects range partitioning... I would expect something like init_range_partitions and you probably can autodetect range_interval in most cases by max(attribute) - min(attribute). Just my 5 cents - thanks for your work.

[vmihailenco]

I get this when I try to use pg_pathman on existing partitions

goab_shard_test=# select public.add_to_pathman_config('notices', 'id', '22468047667200000');
ERROR:  constraint "pathman_545146_1_check" for partition "notices_y2016m01" does not exist

Do you have any ideas?

[funbringer]

To @vmihailenco:

This is due to the fact that HASH constraints have been dropped along with the pg_pathman, since they rely on function provided by the extension. There's nothing you can do to workaround this: once you've dropped pg_pathman, all HASH constraints are destroyed.

We could probably evaluate a hash of a partitioned column and restore check constraints, though. What do you think?

UPD: sorry, my bad, at first I didn't notice that you've tried to import a RANGE-partitioned table. The check constraint naming convention had changed, so pg_pathman can't find a proper constraint. You could call it a 'breaking change', but we had no intention to support deprecated convention of beta release to begin with.

This function is going to be compatible with v1.0+.

[vmihailenco]

This is due to the fact that HASH constraints have been dropped along with the pg_pathman

I did not drop pg_pathman extension. Instead I created new database and new partitions. Also I am not sure which HASH constraints you mean, because I am using partitioning by range...

[funbringer]

to @vmihailenco:

Instead I created new database and new partitions

So you did this (correct me if i'm wrong):

1. installed pg_pathman on new DB;
2. partitioned table by range;
3. deleted corresponding entry from pathman_config;
4. Got an error while trying to use add_to_pathman_config.

Right?

[alexign]

Hi!
Can you explain more thoroughly what do you want to do.
Say I have my own partitioned table and want it migrate to pg_pathman partition scheme?

[funbringer]

To @vmihailenco:

It is a bit strange that function has general name add_to_pathman_config

Actually, it's the main point of this function. It is intended to be used in case when you want to integrate a table which had been partitioned by pg_pathman a while ago, but was removed from pathman_config (e.g. the extension has been dropped). Thus it is designed to check if constraints with certain format and names exist on each partition.

I would expect something like init_range_partitions and you probably can autodetect range_interval in most cases by max(attribute) - min(attribute)

It is up to you to select the interval you'd like to use. It will only be used for new partitions, not the old ones. I don't see a desperate need for this feature, since it would make validation much slower. If you really crave for this feature, you could make a tiny wrapper function to calculate the interval using your formula :)

but expects range partitioning...

Actually, interval parameter is declared as range_interval TEXT DEFAULT NULL, with NULL meaning that table is partitioned by HASH, so this function is universal.

[vmihailenco]

Say I have my own partitioned table and want it migrate to pg_pathman partition scheme?

Exactly. See my very first post.

Thus it is designed to check if constraints with certain format and names exist on each partition.

Okay. My intention here is to migrate existing partitioned table to pg_pathman. So I certainly don't have some constraints. Can you please advise what should I do? On master I could use

select create_partitions_from_range('notices', 'id', 0::bigint, -1::bigint, 22468047667200000);

which seemed to work fine on simple tests...

[funbringer]

To @vmihailenco:

Can you please advise what should I do?

Probably the most generic advice: you could create a completely new non-partitioned table (say, part_new), then partition it using pg_pathman (setting your interval of choice) into 1 partition, and after that:

INSERT INTO part_new SELECT * FROM __your_table__;

And pg_pathman will create new partitions for your data automatically.

[vmihailenco]

Probably the most generic

...obvious and useless - no offense, but my question implied that I want to avoid reinserting several terrabytes of data.