How does partition_data_proc in online make sure new rows that match the partition being created don't get inserted in the default table?

[rguillebert]

Hi

From looking at the code, partition_data_proc calls partition_data_time or partition_data_id, which in turns create a temporary table and copies data to it before creating the partition.

In-between those 2 things, a transaction could insert new rows matching the future partition's range and that row would end up in the default table thus preventing the partition from being created. Is this something pg_partman protects itself against? If so, how does it do it?

Thanks
Romain

[keithf4]

So, I think one of two things would happen.

1. The transaction trying to insert that data will be held until the transaction that is making the new child table finishes. Then the new data would go into the new child.
2. Your case where the transaction running the partition data function will fail once that child table attempts to be created. Would then have to try again. I think if this is the case, then you're likely going to have to pause the application trying to insert that data if it keeps happening. Either that or wait until the application is past the point in time where it's inserting data into the default and use a child table in the future. Might possibly have to manually create that new future child partitions to let things get back to normal.

This is an interesting case that I hadn't actually thought about and may have some relevance to work I'm doing in 5.x. I may have to leave a setting in place to allow a workaround for this. Thanks for bringing it up!

Either way, I don't think PostgreSQL is going to let an inconsistent state exist in the end. Would have to test things out and see which happens.

[KrohnicDev]

We actually ran into this problem recently, luckily not in production. We ended up shutting down the application. For production, creating the future partitions manually would perhaps be a good solution.

I have a related question. Let's imagine a situation like this:

• interval is 1000
• premake is 10
• for some reason, data exists in default partition, so partman fails to create partition 1/10

Will it still create the remaining 9 partitions or will it just stop because the first one failed? We are still running version 4.7.4.

[keithf4]

It will not continue past the failure and will continue to fail each time maintenance is run after that until it is fix.

[keithf4]

So 5.x will ignore data in the default partition by default. But it is done with a flag that is set to true by default (I'd forgotten I'd already done this!). So you could set that to false for the given partition set when this occurs. Once the application moves past inserting data for the child tables that are missing, you should then be able to run partition_data_proc() without it causing any problems. If there still happens to be partitions missing, you can use the partition_gap_fill() function to fix that.

After that, I would recommend resetting the flag so it ignores data in the default for partition maintenance and errors out again. Errant data in the default can cause weird child table generation if it is really far out of bounds. Either way, you should also monitor for data going into the default with the check_default() function to catch both past and future data outside the current bounds getting put into the default.