3

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:

  1. I created the partitions for sessionaccounting based on a starttime
  2. I created the partitions for sessionaccountingdailysplit based on a date
  3. 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?

1 Answer 1

1

I understand that sessionaccountingYEAR tables are children of sessionaccounting. If that is true than you could just reference the parent table in instead of refer to the corresponding YEAR table:

CONSTRAINT sessionaccountingdailysplit2009_sessionaccountingid_fkey 
FOREIGN KEY (sessionaccountingid) 
REFERENCES sessionaccounting (id) 
MATCH SIMPLE

If sessionaccountingYEAR are not the children of sessionaccounting, could you explain why aren't they?

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.