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?