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@illuminatedcomputing.com