Skip site navigation (1) Skip section navigation (2)

Check constraints on partition parents only?

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Check constraints on partition parents only?
Date: 2011-07-26 02:31:03
Message-ID: 8762mp93iw.fsf@comcast.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

I can understand why it makes sense to handle this as an error.

Howeverin practice on a few systems that I used to manage this would
be a problem.

1. I got into the habit of putting CHECK (false) on the parent table
   if it was an always empty base table,

   This is just really documentation indicating that this table can't
   hold rows and of course, having the partition selector trigger
   raise exception if falling through the if/else logic on a new row
   insertion enforces the constraint but is less obvious.

   Ok, so no real problem here.  Just one example.

2. Atypical partitioning implementation where the parent table was for
   initial insert/update of "live" records in an OLTP system with high
   update/insert ratio.  This table was partitioned retroactively in
   such a way transparent to the application.  The app would
   eventually update a row one final time and set a status field to
   some terminal status, at which time we'd fire a trigger to move the
   row down into a partition.  Record expiry took place periodically
   by dropping a partition and creating a new one.

   In that case, imagine the application user runs with
   sql_inheritance to off and so, sees only the live data which
   resulted in a huge performance boost.  Reporting apps and in fact
   all other users ran with sql_inheritance to on as usual and so, see
   all the data.

   Suppose the status field had several non-terminal values and one or
   a few terminal values.  The differing check constraints on parent
   and child tables made it easy to see the intent and I presume with
   constraint_exclusion set to on, let queries on behalf of regular
   users that had specified a non-terminal state visit only the tiny
   parent table.
   
   Parent might have CHECK (status in (1,2,3)) and children CHECK
   (status = 4).

   I'll assume not many sites are architected this way but #2 here
   shows a more compelling example of why it might be useful to allow
   check constraints added to only a partition parent.

   Comments?

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 305.321.1144

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2011-07-26 02:44:32
Subject: Re: Check constraints on partition parents only?
Previous:From: Josh KupershmidtDate: 2011-07-26 02:29:29
Subject: Re: psql: bogus descriptions displayed by \d+

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group