We used a rule that inserted into a partition mod the date.
Then we truncated the partitions before we reused them. This
allowed us to avoid changing the actual trigger. Here is the
trigger we are using for a two month rotation:
DECLARE
table_name text;
table_id_num int;
BEGIN
table_name := TG_ARGV[0];
SELECT INTO table_id_num mod(CAST(EXTRACT(epoch FROM NEW.date) AS INT),63*86400)/86400;
IF table_name = 'table' THEN
IF table_id_num <= 21 THEN
IF table_id_num <= 7 THEN
IF table_id_num = 0 THEN
INSERT INTO table_0 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 1 THEN
INSERT INTO table_1 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 2 THEN
INSERT INTO table_2 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 3 THEN
INSERT INTO table_3 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 4 THEN
INSERT INTO table_4 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 5 THEN
INSERT INTO table_5 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 6 THEN
INSERT INTO table_6 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 7 THEN
INSERT INTO table_7 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 14 THEN
IF table_id_num = 8 THEN
INSERT INTO table_8 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 9 THEN
INSERT INTO table_9 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 10 THEN
INSERT INTO table_10 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 11 THEN
INSERT INTO table_11 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 12 THEN
INSERT INTO table_12 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 13 THEN
INSERT INTO table_13 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 14 THEN
INSERT INTO table_14 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 21 THEN
IF table_id_num = 15 THEN
INSERT INTO table_15 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 16 THEN
INSERT INTO table_16 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 17 THEN
INSERT INTO table_17 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 18 THEN
INSERT INTO table_18 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 19 THEN
INSERT INTO table_19 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 20 THEN
INSERT INTO table_20 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 21 THEN
INSERT INTO table_21 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
END IF;
IF table_id_num <= 42 THEN
IF table_id_num <= 28 THEN
IF table_id_num = 22 THEN
INSERT INTO table_22 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 23 THEN
INSERT INTO table_23 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 24 THEN
INSERT INTO table_24 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 25 THEN
INSERT INTO table_25 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 26 THEN
INSERT INTO table_26 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 27 THEN
INSERT INTO table_27 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 28 THEN
INSERT INTO table_28 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 35 THEN
IF table_id_num = 29 THEN
INSERT INTO table_29 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 30 THEN
INSERT INTO table_30 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 31 THEN
INSERT INTO table_31 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 32 THEN
INSERT INTO table_32 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 33 THEN
INSERT INTO table_33 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 34 THEN
INSERT INTO table_34 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 35 THEN
INSERT INTO table_35 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 42 THEN
IF table_id_num = 36 THEN
INSERT INTO table_36 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 37 THEN
INSERT INTO table_37 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 38 THEN
INSERT INTO table_38 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 39 THEN
INSERT INTO table_39 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 40 THEN
INSERT INTO table_40 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 41 THEN
INSERT INTO table_41 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 42 THEN
INSERT INTO table_42 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
END IF;
IF table_id_num <= 63 THEN
IF table_id_num <= 49 THEN
IF table_id_num = 43 THEN
INSERT INTO table_43 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 44 THEN
INSERT INTO table_44 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 45 THEN
INSERT INTO table_45 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 46 THEN
INSERT INTO table_46 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 47 THEN
INSERT INTO table_47 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 48 THEN
INSERT INTO table_48 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 49 THEN
INSERT INTO table_49 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 56 THEN
IF table_id_num = 50 THEN
INSERT INTO table_50 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 51 THEN
INSERT INTO table_51 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 52 THEN
INSERT INTO table_52 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 53 THEN
INSERT INTO table_53 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 54 THEN
INSERT INTO table_54 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 55 THEN
INSERT INTO table_55 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 56 THEN
INSERT INTO table_56 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
IF table_id_num <= 63 THEN
IF table_id_num = 57 THEN
INSERT INTO table_57 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 58 THEN
INSERT INTO table_58 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 59 THEN
INSERT INTO table_59 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 60 THEN
INSERT INTO table_60 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 61 THEN
INSERT INTO table_61 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 62 THEN
INSERT INTO table_62 VALUES (NEW.*);
RETURN NULL;
END IF;
IF table_id_num = 63 THEN
INSERT INTO table_63 VALUES (NEW.*);
RETURN NULL;
END IF;
END IF;
END IF;
END IF;
RAISE EXCEPTION 'No matching partition for table_name = (%) table_id_num(%)', table_name, table_id_num;
RETURN NULL;
END;
)
Maybe something like this would work for you.
Regards,
Ken
On Tue, Jun 30, 2009 at 01:26:23PM -0400, Bhella Paramjeet-PFCW67 wrote:
> Thanks for the response Richard.
>
> We're trying to simulate rolling window concept in postgres with partitions, without impacting application which does concurrent inserts into partitioned tables. We basically need to roll off data from older partition say 7 days older just to avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules on the fly for future day partitions blocks rows from being inserted into current day partition.
>
> Anyone implemented similar concept in postgres.
>
> Thanks
> Paramjeet kaur
>
> -----Original Message-----
> From: Richard Broersma [mailto:richard.broersma@xxxxxxxxx]
> Sent: Monday, June 29, 2009 12:36 PM
> To: Bhella Paramjeet-PFCW67
> Cc: pgsql-admin@xxxxxxxxxxxxxx
> Subject: Re: Partioning in postgres
>
> On Mon, Jun 29, 2009 at 12:19 PM, Bhella Paramjeet-PFCW67<PBhella@xxxxxxxxxxxx> wrote:
>
> > I tested a scenario where in one terminal window I insert rows in the
> > eventlog table in the BEGIN END loop and do not commit the
> > transaction. In another terminal window I create a new partition but
> > when I create a rule for the new partition it hangs until I commit the
> > transaction(insert) in the first terminal window. ?Just want to know
> > if this is a limitation in postgres partitioning ?
>
> Actually I would say that transactional ddl is a feature of PostgreSQL rather than a limitation. But is sounds like this feature isn't playing well with your current design.
>
> > Is there a work around
> > for this problem? In our production environment we want to ?be able to
> > create new partitions and rule for new partition while data is being
> > inserted into the eventlog table.
>
> You probably need break up your insert transactions into smaller segments that coincide DDL changes.
>
>
> --
> Regards,
> Richard Broersma Jr.
>
> Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
