Re: Exclusion constraints on partitioned tables

On 12/15/22 16:12, Tom Lane wrote:
>> This patch also requires the matching constraint columns to use equality
>> comparisons (`(foo WITH =)`), so it is really equivalent to the existing
>> b-tree rule.
>
> That's not quite good enough: you'd better enforce that it's the same
> equality operator (and same collation, if relevant) as is being used
> in the partition key.
> [snip]
> It might work better to consider the operator itself and ask if
> it's equality in the same btree opfamily that's used by the
> partition key.

Thank you for taking a look! Here is a comparison on just the operator
itself.

I included a collation check too, but I'm not sure it's necessary.
Exclusion constraints don't have a collation per se; it comes from the
index, and we choose it just a little above in this function. (I'm not
even sure how to elicit that new error message in a test case.)

I'm not sure what to do about matching the opfamily. In practice an
exclusion constraint will typically use gist, but the partition key will
always use btree/hash. You're saying that the equals operator can be
inconsistent between those access methods? That is surprising to me, but
I admit op classes/families are still sinking in. (Even prior to this
patch, isn't the code for hash-based partitions looking up ptkey_eqop
via the hash opfamily, and then comparing it to idx_eqop looked up via
the btree opfamily?)

If partitions can only support btree-based exclusion constraints, you
still wouldn't be able to partition a temporal table, because those
constraints would always be gist. So I guess what I really want is to
support gist index constraints on partitioned tables.

Regards,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com