|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
The following caveats apply to partitioned tables:
There is currently no way to verify that all of the
CHECKconstraints are mutually exclusive. Care is required by the database designer.
There is currently no simple way to specify that rows must not be
inserted into the master table. A
CHECK (false)constraint on the master table would be inherited by all child tables, so that cannot be used for this purpose. One possibility is to set up an
ON INSERTtrigger on the master table that always raises an error. (Alternatively, such a trigger could be used to redirect the data into the proper child table, instead of using a set of rules as suggested above.)
The following caveats apply to constraint exclusion:
Constraint exclusion only works when the query's
WHEREclause contains constants. A parameterized query will not be optimized, since the planner cannot know what partitions the parameter value might select at run time. For the same reason, “stable” functions such as
CURRENT_DATEmust be avoided.
Avoid cross-data type comparisons in the
CHECKconstraints, as the planner will currently fail to prove such conditions false. For example, the following constraint will work if
integercolumn, but not if
CHECK ( x = 1 )For a
bigintcolumn we must use a constraint like:
CHECK ( x = 1::bigint )The problem is not limited to the
bigintdata type--it can occur whenever the default data type of the constant does not match the data type of the column to which it is being compared. Cross-data type comparisons in the supplied queries are usually OK, just not in the
- All constraints on all partitions of the master table are considered for constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably.
Don't forget that you still need to run
ANALYZEon each partition individually. A command like
ANALYZE measurement;will only process the master table.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|