Hi, Thanks for your feedback.
On 2016/02/26 0:43, Jean-Pierre Pelletier wrote: > Why not based it on "Exclusion Constraint" ? > > Most discussions as of late seems to focus on Range overlaps which appeal > (I would think) is that it supports both "equality" and "overlaps", two > popular partitioning schemes. > > "Equality" as in "value1 = value2" can be implemented with "range > overlaps" > as "range(value1,value) = range(value,value2)". > > I would think that Partitioning schemes can be Declarative, Efficient and > not restricted to Equality and Overlaps as long as all partitions (of a > partitioned table) are using a single partitioning definition expressed > as: > - An Immutable Expression on tuple columns, in the simplest case a single > column > - An Operator, in the simplest case, "equality" > > That seems very close to the semantic of "Constraint Exclusion" as > described here: > http://thoughts.davisjeff.com/2010/09/25/exclusion-constraints-are-general > ized-sql-unique/ > > If partitioning could be based on EC, it would bring these additional > benefits: > - The choice of operator as long as it is boolean. commutative and > Indexable > - The use of Expression/Function and not just bare columns Note that proposed patch does more or less what you say we should be doing minus the "exclusion constraint" part. With the proposed, you can specify an expression(s)/column(s) as partition key along with an "operator class" for the column like below: CREATE TABLE foo (a type_name) PARTITION BY LIST (a USING opclass_name); CREATE TABLE bar (a type_name) PARTITION BY RANGE (a USING opclass_name); Right now, supported partition strategies are RANGE and LIST where "btree operators" suffice. So in the above example, type_name must have a suitable btree operators class defined in the system which could be opclass_name. If opclass_name was created as the default for type_name, one need not write USING opclass_name. Then when you create a partition of foo: CREATE TABLE foo_partition PARTITION OF foo FOR VALUES IN (val1, val2); system enforces that foo_partition only contains values such that: a = ANY ( ARRAY [val1, val2] ), where the operator "=" refers to an operator belonging to the operator class opclass_name (hence can have a user-defined notion of "equality"). And when you create a partition of bar: CREATE TABLE bar_partition PARTITION OF bar FOR VALUES [val1, val2); system enforces that bar_partition only contains values such that: val1 <= a < val2, where operators "<=" and "<" refer to the operators belonging to the operator class opclass_name (hence can have a user-defined notion of ordering). Further, system can also optimize queries based on its knowledge of operators appearing in query clauses and implicit constraints just mentioned above. Specifically, it can can exclude partitions using "constraint exclusion" which is NOT the same thing as "exclusion constraints", as you might be aware. "Exclusion constraints" depend on having suitable a index (just like unique constraint enforcing btree index) that uses the specified operator to enforce the constraint: postgres=# CREATE TABLE circles ( postgres(# c circle, postgres(# EXCLUDE USING gist (c WITH &&) postgres(# ); CREATE TABLE postgres=# \d+ circles Table "public.circles" Column | Type | Modifiers | Storage | Stats target | Description -------+--------+-----------+---------+--------------+------------- c | circle | | plain | | Indexes: "circles_c_excl" EXCLUDE USING gist (c WITH &&) The talk of "non-overlapping partitions" in this thread refers to the invariant that partition DDL should maintain which uses ad-hoc logic to do that but which is based on the semantics of the specified operators. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
