|
I have several partitions on a history table that are
partitioned by a date range (monthly). However, it’s possible for
an unexpected but valid date (either very far in the future or very far in the
past) to come in the data set and so there is an “overflow” table. Say table A is parent, B is April data, C is June
data, D is July data, and O is overflow data. I set several stored procedures to facilitate the adding of
triggers, constraints, etc for partitions. These procs, in addition to
adding the constraint the normal partitions, also add a “NOT”
constraint to the overflow table. i.e., when the July partition is
created with alter table D add constraint
onlyjuly check (date1 >= ‘2009-07-01’ and date1 <
‘2009-07-01’) Then this is also run alter table O add constraint
notjuly check (NOT(date1 >= ‘2009-07-01’ and date1 <
‘2009-07-01’)) The planner excludes correctly except that it always checks
O. It doesn’t seem to be able to use the multiple constraints
on O. Are multiple “NOT” constraints too much for the
planner for excluding partitions? postgres=# select version();
version ---------------------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled
by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8.0.1), 64-bit Chris Spotts |
