constraint checking on partitions

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

Responses

Browse pgsql-general by date

  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?