Re: Exclusion constraints on partitioned tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Exclusion constraints on partitioned tables
Date: 2022-12-16 00:12:07
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> writes:
> It lets you create exclusion constraints on partitioned tables, similar
> to today's rules for b-tree primary keys & unique constraints:
> just as we permit a PK on a partitioned table when the PK's columns are
> a superset of the partition keys, so we could also allow an exclusion
> constraint when its columns are a superset of the partition keys.

OK. AFAICS that works in principle.

> 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. Remember that we don't have a requirement that
a datatype have only one equality operator; and these days I think
collation can affect equality, too.

Another problem is that while we can safely assume that we know what
BTEqualStrategyNumber means in btree, we can NOT assume that we know
what gist opclass strategy numbers mean: each opclass is free to
define those as it sees fit. The part of your patch that is looking
at RTEqualStrategyNumber seems dangerously broken to me.

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. (Hm, do we use btree opfamilies for all types of

Anyway, I think something can be made of this, but you need to be less
fuzzy about matching the equality semantics of the partition key.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2022-12-16 01:19:26 Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX
Previous Message David Rowley 2022-12-15 23:47:51 Add enable_presorted_aggregate GUC