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