Partitioning attempts - Mailing list pgsql-general

Hi,

I did a partially successful attempt at creating a partitioned table + two subpartitions in Pg8.1b inspired by the
methodused by Bizgres (see http://www.bizgres.org/assets/docs/html/tblpartn.htm) 

I.e:

CREATE TABLE parttest.mastertab
(
  id serial not null,
  datecol date not null,
  CONSTRAINT pk_mastertab PRIMARY KEY (id)
);

CREATE TABLE parttest.mastertab_jan05
(
  CONSTRAINT mastertab_jan05_datecol_check CHECK (datecol between '20050101' AND '20050131')
)
INHERITS (parttest.mastertab);

CREATE TABLE parttest.mastertab_feb05
(
  CONSTRAINT mastertab_jan05_datecol_check CHECK (datecol between '20050201' AND '20050228')
)
INHERITS (parttest.mastertab);


I then added INSERT rules to the mastertab:

create or replace rule "mastertab_insert_0501" as
on insert to mastertab where (NEW.datecol between '20050101' and '20050131')
do instead insert into mastertab_jan05 (id, datecol) values (NEW.id, NEW.datecol);

create or replace rule "mastertab_insert_0502" as
on insert to mastertab where (NEW.datecol between '20050201' and '20050228')
do instead insert into mastertab_feb05 (id, datecol) values (NEW.id, NEW.datecol);


Testing the INSERT rules:

insert into parttest.mastertab (id, datecol)
values (nextval('mastertab_id_seq'), '20050101');

insert into parttest.mastertab (id, datecol)
values (nextval('mastertab_id_seq'), '20050201');

-- where did it go?
select p.relname, m.id, m.datecol
from mastertab m, pg_class p
where m.tableoid = p.oid;

relname                                                    id         datecol
-----------------------------------------------------------------------------
mastertab_jan05                                             1      2005-01-01
mastertab_feb05                                             2      2005-02-01


So far so good. Now, the part where it fails to be useful (performance wise) is when you want to make sure a select
queryonly scans the relevant partition(s), as it's not possible to create a conditional SELECT rule using an INSTEAD
select... from ONLY ..., i.e: 

create or replace rule "mastertab_select_0501" as
on select to mastertab where (datecol between '20050101' and '20050131')
do instead select * from only mastertab_jan05;

Also, we would need UPDATE and DELETE rules, but these are tricky as an update may involve both inserting and deleting
rows(if the row belongs to another child table after the update). 

The steps carried out above could be wrapped by a function to ease the process, something like:

partition_add('mastertable', 'jan05', 'datecol between 20050101 and 20050131', 'tbsp1')

..could create the child table "mastertable_jan05" in tablespace tbsp1 together with the needed rules, but new DDL
syntaxwould of course be better still. 


Ideas/comments?


*******************************************************************
Mikael Carneholm
Systems Engineer
Service Delivery
WirelessCar
Address Kajskjul 107, Frihamnen, SE-417 07 Göteborg, Sweden
Phone + 46 31 65 19 57
Mobile + 46 703 22 29 57
Fax + 46 31 65 19 99
E-mail to_lowercase(my_first_name.my_last_name)(at)wirelesscar.com
Website www.wirelesscar.com
Telematics made easy