Re: Partioning in postgres - Mailing list pgsql-admin

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
concurrentinserts into partitioned tables. We basically need to roll off data from older partition say 7 days older
justto avoid the overhead from delete/vacuum etc, and keep adding future day partitions. Seems like adding rules on the
flyfor 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@gmail.com]
> Sent: Monday, June 29, 2009 12:36 PM
> To: Bhella Paramjeet-PFCW67
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Partioning in postgres
>
> On Mon, Jun 29, 2009 at 12:19 PM, Bhella Paramjeet-PFCW67<PBhella@motorola.com> 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
thisfeature 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>