partition_data_proc and run_maintenance with infinity timestamps

[jw1u1]

partition_data_proc and run_maintenance seems not to handle infinity timestamps.
While
bre=# call partman.partition_data_proc('foo');
ERROR: Attempted partition time interval is outside PostgreSQL's supported time range.
Unable to create partition with interval before timestamp 294277-01-01 00:00:00+01

infinite_time_partitions = false does't help.

Expectation: infinity timestamps should remain in the default partition and new partitions should be created up to max(timestamp) where timestamp != 'infinity'.

[keithf4]

The default partition feature is built into postgresql and the constraint on the default is always the anti-constraint of all other child tables combined. So that will not work to try and assign that boundary to the built in default.

I'm honestly not sure if PostgreSQL itself can support a partition boundary of infinity. Would have to test.

[keithf4]

Please see the documentation for what infinite_time_partitions is for. It doesn't have anything to do with the infinity value. It's to tell pg_partman to keep making time-based partitions infinitely even if there is no new data.

[jw1u1]

Please see the documentation for what infinite_time_partitions is for. It doesn't have anything to do with the infinity value. It's to tell pg_partman to keep making time-based partitions infinitely even if there is no new data.

agree, but could prevent to try to create infinity child tables.

[jw1u1]

I'm honestly not sure if PostgreSQL itself can support a partition boundary of infinity. Would have to test.

postgres=# insert into public.foo_bar (event_cause, event_ts) VALUES ('bar', 'infinity');
INSERT 0 1
postgres=# select * from only foo_bar_default;
id | event_cause | event_ts
----+-------------+----------
3 | bar | infinity

[keithf4]

Yes, the value itself is a supported thing in PostgreSQL. But does that table have any constraint boundaries on the event_ts column?

[jw1u1]

Yes, the value itself is a supported thing in PostgreSQL. But does that table have any constraint boundaries on the event_ts column?

Interesting:
Partition constraint: ((event_cause IS NOT NULL) AND (event_cause = 'bar'::text) AND (NOT ((event_ts IS NOT NULL) AND ((event_ts >= '2021-01-01 00:00:00+01'::timestamp with time zone) AND (event_ts < '2021-12-31 23:59:59+01'::timestamp with time zone)))))

[keithf4]

So looking into this more, I see how you're expecting this to work. However, as you saw, an infinite value cannot be partitioned out. By definition infinity is greater than all possible values, so how would it know which child table to put it into? This would only work on an unbounded child table, which is what the default table is. See example here:

keith=# \d+ mytable_default 
                                      Table "public.mytable_default"
  Column   |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
-----------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 dt        | date    |           | not null |         | plain   |             |              | 
 table_id  | integer |           |          |         | plain   |             |              | 
 date_from | bigint  |           |          |         | plain   |             |              | 
 date_to   | bigint  |           |          |         | plain   |             |              | 
 otec_id   | integer |           |          |         | plain   |             |              | 
 standard  | integer |           |          |         | plain   |             |              | 
 place     | integer |           |          |         | plain   |             |              | 
 count     | bigint  |           |          |         | plain   |             |              | 
Partition of: mytable DEFAULT
Partition constraint: (NOT ((dt IS NOT NULL) AND (((dt >= '2021-03-16'::date) AND (dt < '2021-03-17'::date)) OR ((dt >= '2023-03-23'::date) AND (dt < '2023-03-24'::date)) OR ((dt >= '2023-03-24'::date) AND (dt < '2023-03-25'::date)) OR ((dt >= '2023-03-25'::date) AND (dt < '2023-03-26'::date)) OR ((dt >= '2023-03-26'::date) AND (dt < '2023-03-27'::date)) OR ((dt >= '2023-03-27'::date) AND (dt < '2023-03-28'::date)) OR ((dt >= '2023-03-28'::date) AND (dt < '2023-03-29'::date)) OR ((dt >= '2023-03-29'::date) AND (dt < '2023-03-30'::date)) OR ((dt >= '2023-03-30'::date) AND (dt < '2023-03-31'::date)) OR ((dt >= '2023-03-31'::date) AND (dt < '2023-04-01'::date)))))
Access method: heap

So, technically, it can live in the default since the default is unbound at the lower/upper end. But it can never partition it out because there is no other possible table for it to exist in.

I'm honestly not sure I want to support this, though. The default table should not have values being left in it. The reason why is that every time a new child table is created, the default has to compare all values contained in it against the new child table. And if I tried to support this, the partition_data function would itself also have to check every single value in the default to see if it is infinite or not every time it tries to create a new child table. So either way, it's not very efficient in the long run.

[keithf4]

Perhaps I can make it a flag to the partition_data procedure in 5.x. But just to be sure, infinite values are considered valid in your case and this wasn't errant data that someone put in? Because if it's errant data, then it throwing an error would be preferred and you should just remove it from the default.

[jw1u1]

But just to be sure, infinite values are considered valid in your case and this wasn't errant data that someone put in?

Yes, they are valid.
We could work with null, too.
So, thank you for clarification.

[keithf4]

Ok. I'll see if I can make this an option for the partition_data functions in the future. I'd recommend working with NULL if that's a possibility, though

[keithf4]

Apologies, NULL isn't going to work either. I currently have a requirement that the partitioning column be NOT NULL. Also this would run into the same issue. Which table would the NULL value go into? The only one that it fits in is the default.

[jw1u1]

This is where people start to use 9999-12-31 as a workaround...

[keithf4]

Yes, I can see that. I'll see about adding that option in with a caveat in the docs that it can affect maintenance performance.