Hi, I did a partially successful attempt at creating a partitioned table + two subpartitions in Pg8.1b inspired by the method used 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 query only 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 syntax would 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
