Correct docs about partitions and EXCLUDE constraints
Hi Hackers,
In pg17 we added support for cross-partition EXCLUDE constraints, as long as they included all
partition key columns and compared them with equality (see 8c852ba9a4). I updated the docs for
exclusion constraints, but I missed that the docs for CREATE TABLE still said that they were not
supported. This commit fixes that. I don't see a similar message in the ALTER TABLE docs.
I take it we should apply this patch to both v18 and v17?
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
Attachments:
v1-0001-Correct-docs-about-partitions-and-EXCLUDE-constra.patchtext/x-patch; charset=UTF-8; name=v1-0001-Correct-docs-about-partitions-and-EXCLUDE-constra.patchDownload
From 9436f3bdfe301be9d31d48a8b47eb0e802b67152 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 17 Jun 2025 20:48:56 -0700
Subject: [PATCH v1] Correct docs about partitions and EXCLUDE constraints
In pg17 we added support for cross-partition EXCLUDE constraints, as
long as they included all partition key columns and compared them with
equality (see 8c852ba9a4347c4778cc610ad5a9cb50ea701b5c). I updated the
docs for exclusion constraints, but I missed that the docs for CREATE
TABLE still said that they were not supported. This commit fixes that.
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
doc/src/sgml/ref/create_table.sgml | 7 +++++--
1 file changed, 5 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 4a41b2f5530..62eb273236b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -448,8 +448,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Partitioned tables do not support <literal>EXCLUDE</literal> constraints;
- however, you can define these constraints on individual partitions.
+ Partitioned tables support <literal>EXCLUDE</literal> constraints
+ if the constraint includes all the partition key columns
+ and it compares all those columns for equality. It may include additional
+ columns and compare them however it likes (e.g. with <literal>&&</literal>).
+ This ensures that any rows that might conflict will fall in the same partition.
</para>
<para>
--
2.39.5
On Wed, 2025-06-18 at 11:25 -0700, Paul Jungwirth wrote:
Hi Hackers,
In pg17 we added support for cross-partition EXCLUDE constraints, as
long as they included all
partition key columns and compared them with equality (see
8c852ba9a4). I updated the docs for
exclusion constraints, but I missed that the docs for CREATE TABLE
still said that they were not
supported. This commit fixes that. I don't see a similar message in
the ALTER TABLE docs.
Leaving it in the entry for PARTITION BY seems out of place; shouldn't
it be moved to the entry for EXCLUDE? I also merged your wording with
some similar wording from the entry about UNIQUE. Attached.
Regards,
Jeff Davis
Attachments:
0001-Correct-docs-about-partitions-and-EXCLUDE-constraint.patchtext/x-patch; charset=UTF-8; name=0001-Correct-docs-about-partitions-and-EXCLUDE-constraint.patchDownload
From 0988ec1bac79055899fb555ac0c0441333888c83 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 17 Jun 2025 20:48:56 -0700
Subject: [PATCH] Correct docs about partitions and EXCLUDE constraints.
In version 17 we added support for cross-partition EXCLUDE
constraints, as long as they included all partition key columns and
compared them with equality (see 8c852ba9a4). I updated the docs for
exclusion constraints, but I missed that the docs for CREATE TABLE
still said that they were not supported. This commit fixes that.
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Co-authored-by: Jeff Davis <pgsql@j-davis.com>
Discussion: https://postgr.es/m/c955d292-b92d-42d1-a2a0-1ec6715a2546@illuminatedcomputing.com
Backpatch-through: 17
---
doc/src/sgml/ref/create_table.sgml | 17 ++++++++++++-----
1 file changed, 12 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 4a41b2f5530..a5816918182 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -447,11 +447,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the values in the new row, an error will be reported.
</para>
- <para>
- Partitioned tables do not support <literal>EXCLUDE</literal> constraints;
- however, you can define these constraints on individual partitions.
- </para>
-
<para>
See <xref linkend="ddl-partitioning"/> for more discussion on table
partitioning.
@@ -1162,6 +1157,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
exclusion constraint on a subset of the table; internally this creates a
partial index. Note that parentheses are required around the predicate.
</para>
+
+ <para>
+ When establishing an exclusion constraint for a multi-level partition
+ hierarchy, all the columns in the partition key of the target
+ partitioned table, as well as those of all its descendant partitioned
+ tables, must be included in the constraint definition. Additionally,
+ those columns must be compared using the equality operator. These
+ restrictions ensure that potentially-conflicting rows will exist in the
+ same partition. The constraint may also refer to other columns which
+ are not a part of any partition key, which can be compared using any
+ appropriate operator.
+ </para>
</listitem>
</varlistentry>
--
2.43.0
On 6/19/25 08:43, Jeff Davis wrote:
Leaving it in the entry for PARTITION BY seems out of place; shouldn't
it be moved to the entry for EXCLUDE? I also merged your wording with
some similar wording from the entry about UNIQUE. Attached.
I agree this new location makes more sense. The wording looks good.
Thanks!
--
Paul ~{:-)
pj@illuminatedcomputing.com