One of the most frequiently asked questions about pg_pathman we get is how to specify a user-defined naming scheme for new partitions. Even though there is no standard way to do this, our suggestion is to write a callback function for partitioned table. Being set callback function performs some additional actions right after new partition is created.
A callback function takes JSONB value as an argument which contains some essential information about partition being created. This json may look something like this (for RANGE-partitioned table):
So let’s say we have my_table table which we want to be partitioned by range with one month interval.
And we want our partitions to be named like abc_2016_01, abc_2016_02, etc. In this case callback function may look like this:
createorreplacefunctionmy_callback(paramsjsonb)returnsvoidas$$declarerange_mintimestamp;new_relnametext;beginrange_min:=(params->>'range_min')::timestamp;-- generate new name for partition based on its parent name and its lower boundnew_relname:=format('%s_%s',params->>'parent',to_char(range_min,'YYYY_MM'));-- rename partitionexecuteformat('alter table %s.%s rename to %s',params->>'partition_schema',params->>'partition',new_relname);end$$languageplpgsql;
Now let’s assign the callback for the table, perform partitioning and see what will happen:
Now, renaming partitions isn’t the only usage of callback functions. For example, you can setup the data rotation mechanism as described here. Or automatically distribute partitions between few tablespaces. Let’s see how it can be done:
-- suppose we have three tablespacescreatetablespacets_0location'/path/to/ts_0';createtablespacets_1location'/path/to/ts_1';createtablespacets_2location'/path/to/ts_2';-- we will use this counter to determine tablespace number for new partition-- based on round-robin algorithmcreatesequencets_counter;createorreplacefunctionmy_callback(paramsjsonb)returnsvoidas$$declarets_nametext;begin-- calculate tablespace namets_name='ts_'||nextval('ts_counter')%3;-- move partition to the tablespaceexecuteformat('alter table %s.%s set tablespace %s',params->>'partition_schema',params->>'partition',ts_name);end$$languageplpgsql;-- create table and partition itcreatetablemy_table(idserial);selectset_init_callback('my_table','my_callback(jsonb)');selectcreate_range_partitions('my_table','id',1,100,7);
Yep, partitions were distributed between tablespace as required. And all partitions that will be created in future will also be destributed the same way.
Another use case for callback functions is to keep the most recent data in a faster tablespace resided on SSD and move old data to a slower tablespace on HDD. The only pitfall here is that this operation can be pretty time consuming and will lock the transaction which triggers partition creation. So it would be better for callback function to add a task to a schedule and leave all the hard work to a scheduler.