[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Partitioning attempts
From:       "Mikael Carneholm" <Mikael.Carneholm () WirelessCar ! com>
Date:       2005-10-31 22:24:12
Message-ID: 7F10D26ECFA1FB458B89C5B4B0D72C2B0881FC () sesrv12 ! wirelesscar ! com
[Download RAW message or body]

> 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 table and \
delegate to the mastertab INSERT rule to take care of insertion into the correct \
child table.

- Mikael

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


[prev in list] [next in list] [prev in thread] [next in thread]