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
selectortrigger raise exception if falling through the if/else logic on a new row insertion enforces the constraint
butis 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
OLTPsystem 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
whichtime we'd fire a trigger to move the row down into a partition. Record expiry took place periodically by
droppinga 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
resultedin a huge performance boost. Reporting apps and in fact all other users ran with sql_inheritance to on as
usualand so, see all the data.
Suppose the status field had several non-terminal values and one or a few terminal values. The differing check
constraintson 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
usefulto allow check constraints added to only a partition parent.
Comments?
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144
