Partitions are not being created when exists one with date which larger than incoming data have.

[hitrome]

I have a data flow from some hardware. But some devices have invalid datetime settings (or emmit data packets with invalid datetime). Thus the packets can be with 2027-year date. So no partitions are being created with date less than 2027-year.
The run_mantenance() proceeds by pg_jobs without any errors. Besides I complited "update partman.part_config set infinite_time_partitions = true;".
How to fix this issue?

[keithf4]

I assume this data is going into the default table then? You'll need to either clean up that invalid data by deleting it from the default or use the partition_data_proc() procedure to move the data out to the proper child tables. If maintenance caused a future child table, you can just drop it if you don't need it or you can use the partition_gap_fill() function to create all intervening child tables. If you find yourself keeping this data, and your partition interval is weekly, monthly, or greater, I'd say just go ahead and let it make all the future child tables you need to account for the "invalid" data (set premake much higher).

I also recommend setting up another monitoring metric using the check_default() function to watch for data getting inserted to any partition set's default. This will alert you to whenever this happens to act upon as soon as you can .

FYI, for version 5.x (coming out soon!), I've changed the default behavior of maintenance to ignore data in the default when making future child tables. I won't be back patching this because it is a change in core behavior I didn't want to do until a new major release. You'll still want to monitor the default table for data to avoid performance issues. But whenever a child table tries to be added that matches data in the default, it will then fail and should alert your job monitoring then as well.

[hitrome]

Thank you, Keith, for the answer!
Yes, all the data with datetime less than 2027y is in the default table :-( . And I have to stop an incoming data process periodically to run select * from partman.partition_data_time('public.fvf_photo_context');. This takes a lot of time because this table contains binary data (from 30k to 200k per record) and has more than 5 million records per day. The partition interval is daily so partition_gap_fill() will create a mass of partitions till 2027-th year.. And I cannot cat off the data with invalid datetime value because this is an application for device monitoring purposes... I must keep any data for some time...
So I think I'll have to create a trigger on insert to check if the partition exists and create it if not... Not a good solution.
Also check_default(); can be used in such a situation but in a slitely modified variant - I'd like add second argument to this function: maximum number of the partition to be created..

[keithf4]

Doing a trigger to create on demand can cause A LOT of contention, especially with any table with a constant flow of data. One transaction will try to create the table, another will come in to try to do the same before the first is done, causing a chain of potential lock waits until the first finishes. This is why pg_partman has never gone with this strategy and opted for the premake.

check_default() does not make any partitions. It just checks to see if any data exists in the default and reports a number; either a simple flag or a full accurate count of rows (which can take a lot longer). Unless I'm not understanding what you're asking.

[keithf4]

Just curious if you've perhaps tried doing weekly partitioning instead of daily? Monthly would potentially be too large depending on the resources available, but 30-40 million rows in a table usually isn't too bad to manage. Especially if this is insert-only data? That could make premaking further out into the future a little easier.

[hitrome]

check_default() does not make any partitions.

Sorry :-) I meant partition_gap_fill();:-) Just mixed all over :-))

if you've perhaps tried doing weekly partitioning instead of daily?

Perhaps you're right... About 200 partitions are better than thousand and more... But theoretically a record can happen with date for example over 2040-th year...

Especially if this is insert-only data?

Not quite.. These records are images which can be displayed to some operators... And besides there are at least one microservice which is crolling across the table to geather some intermediate statistic...

I think I know how to do with this... I'm going to recreate the table with partitioning by date of record creation. Big deal to move an existed data though.. But I think it's the best solution..