Hooking into partition management process

[1803]

In some cases it is required to perform custom actions when a partition related action is performed, such as populate triggers on partition creation or remove certain triggers (or as in my case, include it into logical replication set) when partition is detached.

It seems though event triggers can solve this issue if used correctly, but I feel like a built in solution would fit here.

[funbringer]

Hi @1803,

Thanks for your feedback!

We could possibly add two columns to 'pathman_config' to store regprocedures (basically, function Oids) to be called on partition construction & destruction. For example, one could create plpgsql functions:

custom_init(relid regclass);
custom_fini(relid regclass);

which would be called whenever partition is attached (constructed) or detached(destructed). Would that suit your case?

[1803]

That sounds great. Is it possible to make some variables visible inside those functions?
For example, since these functions are going to be called from corresponding service function of pg_pathman I'd assume that parent_oid, partition_type along with other potentially useful variables are available. Being able to use those would be very nice. (just like NEW.* in regular trigger functions)

[funbringer]

I think we could pass all available parameters directly to a callback. It would be fairly easy to implement a callback system with a fixed function signature. Could you provide us with a full list of parameters you would like to be able to use?

[funbringer]

Alternatively we could create a custom type containing everything you might want to observe (kinda reflection), but it would definitely take more time.

[1803]

I honestly don't see a need in a new type. In my implementation I just passed some variables as arguments to my custom function as I saw fit. Custom type makes it easier to upgrade to a newer version with more parameters but I honestly don't see many more parameters that are of any use here, so your initial proposal seems good enough.

On a quick glance, here's a list of variables:
Parent info:

• parent_oid,
• parttype

Should be enough, since these two seem to identify a record in pathman_config table (the rest can be queried as needed), although this might not be the case and then we'd need a row from pathman_config.

Child info:

• child_oid,
• start_value,
• end_value,
• interval (for range partition only)

[funbringer]

That sounds reasonable, thanks! We're going to release soon, so this will have to wait. In meantime you're welcome to propose any new ideas (regarding the implementation details) here.

[funbringer]

This feature has been added to our roadmap.

[zilder]

Hi,

I've added support for callbacks on partition creation. It is still in development and probably interface may change slightly but the main idea is the same as described above. It only works for RANGE partitioned relation so far since HASH partitioning doesn't allow to add/remove partitions. So there is a new function set_callback() which enables you to specify your own callback function for handling partition creation events. Your callback function must meet certain requirements particularly:

• it only accept one argument of type JSONB;
• return type of function is VOID.

We decided to use jsonb object to pass all necessary parameters as it allows us to pass additional info in future without breaking existing user callback functions.

Here is a simple example:

create table log(id serial, message text);
create function abc_on_partition_created_callback(args jsonb)
returns void as
$$
begin
    insert into log (message) values (args);
end
$$ language plpgsql;

create table abc(a serial, b int);
select create_range_partitions('abc', 'a', 1, 100, 3);
select set_callback('abc', 'abc_on_partition_created_callback');
insert into abc (a) values (350);

select * from log;
 id |                                               message                                               
----+-----------------------------------------------------------------------------------------------------
  1 | {"end": 401, "start": 301, "parent": 183672, "part_type": 2, "partition": 183693, "value_type": 23}
(1 row)

This feature wasn't merged into master yet. But you may try it from callbacks branch on github. Does this implementation works for you?

ps: I'll be out of town for the next two weeks and won't have a laptop with me. So if you'll have questions or suggestions I'll answer as soon as I get back.

[1803]

This is great. Thanks a lot.
I will do some testing later today.
Does calling split_range_partition/merge_range_partitions also trigger these?

[zilder]

Split invokes callback, but merge doesn't since it doesn't create a new partition. This is interesting question should the merge function trigger the callback? And should there be a callback on partitions removed?