p_epoch - Is it possible to support nanoseconds

[Nana-EC]

I know pg_partman does support columns that store timestamps as integers via p_epoch set to "seconds" or "milliseconds".
How easy would it be to add nanoseconds support?
Or are there options that exist to allow us to manage the conversion from nanoseconds to milliseconds whiles still using your official release and without having to create a duplicate conversion column?

Our product does indeed operate at this level of accuracy and the lack of nanoseconds support is blocking us from picking up the great features you've implemented.

Any ideas or plans here?

[keithf4]

Would likely just be a matter of adding another valid option in for the v_partition_expression variable with the proper math to convert an epoch into nanoseconds

One example:
https://github.com/pgpartman/pg_partman/blob/master/sql/functions/create_partition_time.sql#L121

Doing a search for the when condition show likely places it would need to be adjusted for:

pg_partman/sql$ grep -ir "WHEN v_epoch =" *
functions/partition_data_time.sql:    WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
functions/partition_data_time.sql:    WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
functions/undo_partition.sql:    WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
functions/undo_partition.sql:    WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
functions/create_partition_time.sql:    WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
functions/create_partition_time.sql:    WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
functions/create_function_time.sql:        WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
functions/create_function_time.sql:        WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
functions/create_function_time.sql:    WHEN v_epoch = 'seconds' THEN format('to_timestamp(NEW.%I)', v_control)
functions/create_function_time.sql:    WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((NEW.%I/1000)::float)', v_control)

And also the epoch column in part_config would need its constraint adjusted

https://github.com/pgpartman/pg_partman/blob/master/sql/tables/tables.sql#L142

I don't know when I'll have a chance to work on this, but if you'd like to work on a PR I'd be glad to review and try to incorporate it.

[Nana-EC]

Thanks @keithf4.
It's encouraging to know it won't be such an invasive change but I'm sure the devil is in the details.
I'll circle back when I have some spare cycles, whiles I consider an interim solution that unblocks our product until such a PR gets merged and made available.
Thanks

[keithf4]

Was working on a new update so figured I'd try to add this in. I have a testing branch for the next version up if you'd like to test it out. Please do not install this for production since there could still be changes to this released version if anything else comes up.

https://github.com/pgpartman/pg_partman/tree/4.6.1

[keithf4]

Just curious if you may get a chance to look at this and see if it works for you. I understand holidays may have made it more challenging the last few weeks.

[Nana-EC]

Hi @keithf4
Unfortunately wasn't able to get round to this.
I'm back at work next week after the holiday and will try to test locally during my ramp up week.
Is there a quick docker flow to test the extension i.e. spins up a container with postgres and the local repo branch installed as an extension?

[keithf4]

No problem. Unfortunately I don't have a docker setup for this at the moment.

[keithf4]

Just checking to see if you were able to test this option out.

[Nana-EC]

Finally got around to this.
I was able to verify the new functionality.

I created a table similar to our schema

create table if not exists transaction
(
    consensus_timestamp bigint   not null,
    entity_id           bigint,
    result              smallint not null,
    type                smallint not null
) partition by range (consensus_timestamp);

Ran select create_parent('public.transaction', 'consensus_timestamp', 'native', 'weekly', '{"type"}', p_epoch := 'nanoseconds' , p_premake := 2, p_start_partition := to_char(CURRENT_TIMESTAMP-'8 weeks'::interval, 'YYYY-MM-DD HH24:MI:SS'));

Loaded some data 8 weeks, 6 weeks and 4 weeks prior and at current timestamp.
I was able to verify 4 non empty partitions containing the expected data.

Thanks for this and sorry it took soo long to verify.

[keithf4]

Great to hear! When I get a chance to work on the next release, this will be included. No ETA at this time.