Postgres Partitioning: Edge case issue on date column partitioning

Question

I am using Postgres 9.5

I have tables with date column. All tables are partitioned based on the date column.

Table setup:

Example of current partitioned tables are like below

ORDER (id, description, orderstatus, submitteddate) --> submitteddate default value is current date

ORDER_LINE (lineid, orderid, description, submitteddate) --> orderid is FK from ORDER(id)
(multiple order lines inside each order)

PLAN (planid, orderlineid, description, submitteddate) --> orderlineid is FK from ORDER_LINE(lineid)
(Plan generated for each order line)

Tables are partitioned like below

CREATE TABLE ORDER_NOV2016 (CHECK ( submitteddate < '2016-12-01' )) INHERITS (ORDER); 
CREATE TABLE ORDER_DEC2016 (CHECK ( submitteddate < '2017-01-01' )) INHERITS (ORDER);

CREATE TABLE ORDER_LINE_NOV2016 (CHECK ( submitteddate < '2016-12-01' )) INHERITS (ORDER_LINE); 
CREATE TABLE ORDER_LINE_DEC2016 (CHECK ( submitteddate < '2017-01-01' )) INHERITS (ORDER_LINE);

Failing Edge Case

Since partition for inserting data is decided based on submitteddate which is a current date, there will be a situation where an order comes at 2016-11-30 at 11:59PM and data in ORDER table is inserted in NOV2016 partition but data in ORDER_LINE and PLAN table is inserted on DEC2016 partition as by the time inserts are done, date may change in the system.

When I try to drop Nov2016 partition from all tables (child first due to FK constraint), ORDER_LINE and PLAN table drop partition might go through but ORDER table partition drop will fail as orderid from Nov2016 partition would be pointing to the DEC2016 partition data in other 2 tables.

How do I make sure that the orders inserted on date change still goes to same partition across all tables?

Added info (based on @dezso reply)

Dezso's transaction suggestions makes sense. But to make question concise, I left some details. With those details, the suggested solution might differ a bit.

Application supports 2 databases, Oracle and Postgres. For Oracle, partitioning has been implemented using Reference partitioning with partitioned ORDER table and child tables are referenced partitioned based on foreign keys.

For postgres, since there is no reference partitioning option like Oracle, each table was supposed to be individually partitioned using submitteddate. The plan was not to add submitteddate to each table but to use inheritance like below

ORDER_PARTITION(submitteddate date not null default CURRENT_DATE);
ORDER (id, description, orderstatus) INHERITS (order_partition);
ORDER_LINE (lineid, orderid, description) INHERITS (order_partition);
PLAN (planid, orderlineid, description)INHERITS (order_partition);

This way application will not insert submitteddate from the code to support Oracle table structure, instead for postgres its a default value which will get added automatically when a row is inserted in each partitioned table.

Therefore calculating single date at the beginning of the transaction would be difficult as this will require changes on Oracle side as well to add date column to each table.

Is there any other option that is possible?

Answer 1

The check constraints of your partitions are strange. They don't exclude the possibility of an order from DEC2015 being inserted into the JAN2016 table. This might not be an issue about inserting, but it makes constraint exclusion not working nicely. Fix this by specifying a beginning date, too.

That aside, the problem of having an order and its lines in the corresponding partitions can be easily solved. Things depend a bit on how the submitteddate is calculated.

First, in the simple case I would not add such a date to the order lines - the timestamp of the order usually defines the former. However, if there is a possibility to add lines later, your problem is inherently unsolvable: you either cheat and register lines from the next month with an earlier date, or have lines for a given order in different partitions.

On the technical side, ensuring that all dates are from the same day is easy. Use a transaction to add the order lines alongside the order (this makes sense in any case), and in this case CURRENT_DATE will return the date of the start of the transaction, even if the last insert flow over into the next month.