Re: partitionning

Back to the original question on this thread, and using PG 8.0.1.
Perhaps someone would like to poke holes in this (other than the need
to set up fkeys and indexes on the inherited tables...):

-------------------------------------------------------------------------------

begin;
create schema partition_test;
set search_path to partition_test,public;

create table test_base ( id serial, value text, partitioner timestamp
with time zone default now());
create table test_2005_03 () inherits (test_base);
create table test_2005_04 () inherits (test_base);

create rule base_partitioner_test_2005_03 as on insert
to test_base where partitioner AT TIME ZONE 'UTC' between
'2005-03-01'::timestamp and '2005-04-01'::timestamp
do instead insert into test_2005_03 values (NEW.*);

create rule base_partitioner_test_2005_04 as on insert
to test_base where partitioner AT TIME ZONE 'UTC' between
'2005-04-01'::timestamp and '2005-05-01'::timestamp
do instead insert into test_2005_04 values (NEW.*);

insert into test_base (value) values ('first string');
insert into test_base (value, partitioner) values ('a string',
'2004-01-30 10:17:08');
insert into test_base (value, partitioner) values ('a string',
'2005-04-01 14:17:08');

explain analyze select * from test_base;
select tableoid::regclass,* from test_base;

rollback;

-------------------------------------------------------------------------------

This could easily be wrapped up in a
'create_partition_by_date(base_table,partition_start,partition_length)'
function, I just haven't gotten around to that part yet. The function
could even look up the indexes and fkeys on the base table using the
INFORMATION_SCHEMA views.

One thing to note about PG not having indexes across tables, if we do
get in-memory bitmap indexes I believe that the indexes on each
inherited table would actually be combined (if the planner sees it as
a win).

Comments?

On Thu, 10 Mar 2005 12:59:35 +0100, Karsten Hilbert
<Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
> > Actually I have a strong feeling what really _ought_ to happen here is that
> > the inherited tables support in postgres, which never really worked anyways,
> > should be deprecated and eventually removed.
> Hopefully not. They are useful for other things, too.
>
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org