Currently have 2 database tables sessionaccounting and sessionaccountingdailysplit.
Each of these tables have data in them. Sessionaccountingdailysplit has a FK constraint to sessionaccounting.
I need to partition both tables so that the final structure looks like this:
sessionaccounting sessionaccounting2007 sessionaccounting2008 sessionaccounting2009 sessionaccounting2010 sessionaccounting2011 sessionaccounting2012 sessionaccountingdailysplit sessionaccountingdailysplit2009 sessionaccountingdailysplit2010 sessionaccountingdailysplit2011 sessionaccountingdailysplit2012
Reason sessionaccountingdailysplit starts at 2009 is thats when the table was created
The process I followed to do the above is as follows:
- I created the partitions for sessionaccounting based on a starttime
- I created the partitions for sessionaccountingdailysplit based on a date
- I created the constraint
CONSTRAINT sessionaccountingdailysplit2009_sessionaccountingid_fkey FOREIGN KEY (sessionaccountingid) REFERENCES sessionaccounting2009 (id) MATCH SIMPLE
The above seems like it would work but due to the nature of how the dailysplit works its does not. How does dailysplit work? It takes the starttime and endtime from sessionaccounting and splits the data into individual rows.
So if I had a session that is from 2009-01-01 23:30:00 to 2009-01-03 13:00:00 the dailysplit creates 3 rows with each date in it so 2009-01-01, 2009-01-02, 2009-01-03.
Now with the above FK this would work because i know the partitioned year that the rows must go into. The problem comes when I have the following scenario of 2009-12-31 to 2010-01-01.
Since the sessionaccounting row is in sessionaccounting2009 the FK works as sessionaccountingdailysplit2009 has a FK to sessionaccounting2009 but when postgres tries to insert the new row into sessionaccountingdailysplit2010 it cant find the FK to sessionaccounting2010 as its in sessionaccounting2009.
What I need help with is to make a rule or trigger that will insert data into the sessionaccountingdailysplit2009 even though the rows date is 2010. So insert into sessionaccountingdailysplit table where it corresponds to which table the sessionaccounting row is in as to not violate fk constraints.
Is this possible?
A visual example
sessionaccounting2009 1, starttime:2009-12-31, endtime:2010
What I want
sessionaccountingdailysplit2009 1, date:2009-12-31 2, date:2010-01-01
What I get (this violates the FK in 2010)
sessionaccountingdailysplit2009 1, date:2009-12-31 sessionaccountingdailysplit2010 1, date:2010-01-01
The rule I use when inserting data but is causing the problem
CREATE OR REPLACE RULE sessionaccountingdailysplit2009_insert AS
ON INSERT TO sessionaccountingdailysplit
WHERE new.date >= '2009-01-01 00:00:00+02'::timestamp with time zone
AND new.date < '2010-01-01 00:00:00+02'::timestamp with time zone
DO INSTEAD
INSERT INTO sessionaccountingdailysplit2009
(id, sessionaccountingid, date, inputoctets
, outputoctets, privcreditsused, usercreditsused)
VALUES (new.id, new.sessionaccountingid, new.date, new.inputoctets
,new.outputoctets, new.privcreditsused, new.usercreditsused
)
;
Any help to sort out this?