Обсуждение: Partitioning attempts

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

On Mon, 2005-10-31 at 21:02 +0100, Mikael Carneholm wrote:
> 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: 

There is no need to do this.

Try doing a select against mastertab and you will see that it only
selects from the correct partitions. Look at constraint_exclusion
parameter, which needs to be set on for this to work.

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

Not implemented in 8.1

Best Regards, Simon Riggs

> Try doing a select against mastertab and you will see that it only
> selects from the correct partitions. Look at constraint_exclusion
> parameter, which needs to be set on for this to work.

Ah - constraint_exclusion is the correct param, not enable_constraint_exclusion (as stated in
http://www.bizgres.org/assets/docs/html/tblpartn.htm).Btw, does enabling constraint_exclusion come with some sort of
penalty?

And what about these then? (as UPDATE rules for jan05 values):

create or replace rule "mastertab_update_jan05_values" as
on update to mastertab where (OLD.datecol between '20050101' and '20050131')
do instead update mastertab_jan05 set datecol = NEW.datecol where id = OLD.id;

create or replace rule "mastertab_jan05_update" as
on update to mastertab_jan05 where (NEW.datecol < '20050101' or NEW.datecol > '20050131')
do instead (
   delete from mastertab_jan05 where id = OLD.id;
   insert into mastertab (id, datecol) values (OLD.id, NEW.datecol);
)

Apart from the "ERROR:  syntax error at end of input at character 196" message for the mastertab_jan05_update rule
(this*should* be valid syntax according to the CREATE RULE synopsis?), that rule would delete the row from the child
tableand delegate to the mastertab INSERT rule to take care of insertion into the correct child table. 

- Mikael