pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Correct docs about partitions and EXCLUDE constraints
3+ messages / 2 participants
[nested] [flat]

Commits from this thread:
  2025-06-19 6c29088fc6 0f5ac7f321  Correct docs about partitions and EXCLUDE constraints.
* Correct docs about partitions and EXCLUDE constraints
@ 2025-06-18 18:25  Paul Jungwirth <pj@illuminatedcomputing.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Paul Jungwirth @ 2025-06-18 18:25 UTC (permalink / raw)
  To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

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.patch (text/x-patch, 1.6K) download 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>&amp;&amp;</literal>). + This ensures that any rows that might conflict will fall in the same partition. </para> <para> -- 2.39.5
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: Correct docs about partitions and EXCLUDE constraints
@ 2025-06-19 15:43  Jeff Davis <pgsql@j-davis.com>
  parent: Paul Jungwirth <pj@illuminatedcomputing.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Jeff Davis @ 2025-06-19 15:43 UTC (permalink / raw)
  To: Paul Jungwirth <pj@illuminatedcomputing.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

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.patch (text/x-patch, 2.5K) download 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
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: Correct docs about partitions and EXCLUDE constraints
@ 2025-06-19 19:09  Paul Jungwirth <pj@illuminatedcomputing.com>
  parent: Jeff Davis <pgsql@j-davis.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Paul Jungwirth @ 2025-06-19 19:09 UTC (permalink / raw)
  To: Jeff Davis <pgsql@j-davis.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2025-06-19 19:09 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-18 18:25 Correct docs about partitions and EXCLUDE constraints Paul Jungwirth <pj@illuminatedcomputing.com>
2025-06-19 15:43 ` Jeff Davis <pgsql@j-davis.com>
2025-06-19 19:09   ` Paul Jungwirth <pj@illuminatedcomputing.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox