From: | "Chris Spotts" <rfusca(at)gmail(dot)com> |
---|---|
To: | "'postgres list'" <pgsql-general(at)postgresql(dot)org> |
Subject: | constraint checking on partitions |
Date: | 2009-07-09 18:33:03 |
Message-ID: | 00b201ca00c3$b2b06d90$181148b0$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2009-07-09 18:40:37 | Re: ZFS prefetch considered evil? |
Previous Message | Bill Moran | 2009-07-09 17:40:27 | Re: is autovacuum recommended? |