Exclusion constraints on partitioned tables

From: Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Exclusion constraints on partitioned tables
Date: 2022-12-15 23:33:34
Message-ID: ec8b1d9b-502e-d1f8-e909-1bf9dffe6fa5@illuminatedcomputing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello Hackers,

I'm trying to get things going again on my temporal tables work, and
here is a small patch to move that forward.

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.

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. Perhaps that is more conservative than necessary, but we
can't permit an arbitrary operator, since some might require testing
rows that fall into other partitions. For example `(foo WITH <>)` would
obviously cause problems.

The exclusion constraint may still include other columns beyond the
partition keys, and those may use equality operators or something else.

This patch is required to support temporal partitioned tables, because
temporal tables use exclusion constraints as their primary key.
Essentially they are `(id WITH =, valid_at with &&)`. Since the primary
key is not a b-tree, partitioning them would be forbidden prior to this
patch. But now you could partition that table on `id`, and we could
still correctly validate the temporal PK without requiring rows from
other partitions.

This patch may be helpful beyond just temporal tables (or for DIY
temporal tables), so it seems worth submitting it separately.

Yours,

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

Attachment Content-Type Size
v1-0001-Allow-some-exclusion-constraints-on-partitions.patch text/x-patch 20.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joseph Koshakow 2022-12-15 23:43:29 Re: Infinite Interval
Previous Message David Christensen 2022-12-15 23:17:46 Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL