Postgres Partitions Limitations (5.11.2.3)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/ddl-partitioning.html
Description:
Link:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
"Using ONLY to add or drop a constraint on only the partitioned table is
supported as long as there are no partitions. Once partitions exist, using
ONLY will result in an error. Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent table)
dropped." This seems in contradiction to the example involving adding a
unique constraint while minimizing locking at the bottom of "5.11.2.2.
Partition Maintenance", which seems to run fine on my local Pg instance:
"
This technique can be used with UNIQUE and PRIMARY KEY constraints too; the
indexes are created implicitly when the constraint is created. Example:
```ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...
```
"
I might be misinterpreting something. Sorry if that's the case!
Thanks,
Bryce
On Fri, 2023-01-06 at 08:28 +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/ddl-partitioning.html
Description:Link:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE"Using ONLY to add or drop a constraint on only the partitioned table is
supported as long as there are no partitions. Once partitions exist, using
ONLY will result in an error. Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent table)
dropped." This seems in contradiction to the example involving adding a
unique constraint while minimizing locking at the bottom of "5.11.2.2.
Partition Maintenance", which seems to run fine on my local Pg instance:"
This technique can be used with UNIQUE and PRIMARY KEY constraints too; the
indexes are created implicitly when the constraint is created. Example:```ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...
```
"I might be misinterpreting something. Sorry if that's the case!
No, that is actually an omission in the documentation.
The attached patch tries to improve that.
Yours,
Laurenz Albe
Attachments:
0001-Fix-omission-in-partitioning-limitation-documentatio.patchtext/x-patch; charset=UTF-8; name=0001-Fix-omission-in-partitioning-limitation-documentatio.patchDownload
From ecdce740586e33eeb394d47564b10f813896ff11 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Mon, 9 Jan 2023 16:38:58 +0100
Subject: [PATCH] Fix omission in partitioning limitation documentation
UNIQUE and PRIMARY KEY constraints can be created on ONLY the
partitioned table. We already had an example demonstrating that,
but forgot to mention it in the documentation of the limits of
partitioning.
Author: Laurenz Albe
Discussion: https://postgr.es/m/167299368731.659.16130012959616771853@wrigleys.postgresql.org
---
doc/src/sgml/ddl.sgml | 5 ++++-
1 file changed, 4 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 6e92bbddd2..b4a75f9c8f 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4282,7 +4282,10 @@ ALTER INDEX measurement_city_id_logdate_key
Using <literal>ONLY</literal> to add or drop a constraint on only
the partitioned table is supported as long as there are no
partitions. Once partitions exist, using <literal>ONLY</literal>
- will result in an error. Instead, constraints on the partitions
+ will result in an error (the exception to this are
+ <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>
+ constraints, which will be created with an invalid index, as shown in
+ the example above). Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent
table) dropped.
</para>
--
2.39.0
On Mon, 2023-01-09 at 16:40 +0100, Laurenz Albe wrote:
"Using ONLY to add or drop a constraint on only the partitioned table is
supported as long as there are no partitions. Once partitions exist, using
ONLY will result in an error. Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent table)
dropped." This seems in contradiction to the example involving adding a
unique constraint while minimizing locking at the bottom of "5.11.2.2.
Partition Maintenance", which seems to run fine on my local Pg instance:This technique can be used with UNIQUE and PRIMARY KEY constraints too; the
indexes are created implicitly when the constraint is created. Example:No, that is actually an omission in the documentation.
The attached patch tries to improve that.
I am sending a reply to the hackers list, so that I can add the patch to the commitfest.
Yours,
Laurenz Albe
That looks good to me!
The new status of this patch is: Ready for Committer
On Thu, Nov 9, 2023 at 10:00 PM shihao zhong <zhong950419@gmail.com> wrote:
That looks good to me!
The new status of this patch is: Ready for Committer
I have reviewed the patch and it is working fine.
Thanks and Regards,
Shubham Khanna.
On Fri, Oct 27, 2023 at 12:28 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2023-01-09 at 16:40 +0100, Laurenz Albe wrote:
"Using ONLY to add or drop a constraint on only the partitioned table is
supported as long as there are no partitions. Once partitions exist, using
ONLY will result in an error. Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent table)
dropped." This seems in contradiction to the example involving adding a
unique constraint while minimizing locking at the bottom of "5.11.2.2.
Partition Maintenance", which seems to run fine on my local Pg instance:This technique can be used with UNIQUE and PRIMARY KEY constraints too; the
indexes are created implicitly when the constraint is created. Example:No, that is actually an omission in the documentation.
The attached patch tries to improve that.
I am sending a reply to the hackers list, so that I can add the patch to the commitfest.
May be attach the patch to hackers thread (this) as well?
--
Best Wishes,
Ashutosh Bapat
On Thu, 2023-11-30 at 19:22 +0530, Ashutosh Bapat wrote:
May be attach the patch to hackers thread (this) as well?
If you want, sure. I thought it was good enough if the thread
is accessible via the commitfest app.
Yours,
Laurenz Albe
Attachments:
0001-Fix-omission-in-partitioning-limitation-documentatio.patchtext/x-patch; charset=UTF-8; name=0001-Fix-omission-in-partitioning-limitation-documentatio.patchDownload
From ecdce740586e33eeb394d47564b10f813896ff11 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Mon, 9 Jan 2023 16:38:58 +0100
Subject: [PATCH] Fix omission in partitioning limitation documentation
UNIQUE and PRIMARY KEY constraints can be created on ONLY the
partitioned table. We already had an example demonstrating that,
but forgot to mention it in the documentation of the limits of
partitioning.
Author: Laurenz Albe
Discussion: https://postgr.es/m/167299368731.659.16130012959616771853@wrigleys.postgresql.org
---
doc/src/sgml/ddl.sgml | 5 ++++-
1 file changed, 4 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 6e92bbddd2..b4a75f9c8f 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4282,7 +4282,10 @@ ALTER INDEX measurement_city_id_logdate_key
Using <literal>ONLY</literal> to add or drop a constraint on only
the partitioned table is supported as long as there are no
partitions. Once partitions exist, using <literal>ONLY</literal>
- will result in an error. Instead, constraints on the partitions
+ will result in an error (the exception to this are
+ <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>
+ constraints, which will be created with an invalid index, as shown in
+ the example above). Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent
table) dropped.
</para>
--
2.39.0
On Thu, Nov 30, 2023 at 10:29 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2023-11-30 at 19:22 +0530, Ashutosh Bapat wrote:
May be attach the patch to hackers thread (this) as well?
If you want, sure. I thought it was good enough if the thread
is accessible via the commitfest app.
The addition is long enough that it deserved to be outside of parentheses.
I think it's worth mentioning the exception but in a way that avoids
repeating what's mentioned in the last paragraph of just the previous
section. I don't have brilliant ideas about how to rephrase it.
Maybe "Using ONLY to add or drop a constraint, other than PRIMARY and
UNIQUE, on only the partitioned table is supported as long as there
are no partitions. ...".
--
Best Wishes,
Ashutosh Bapat
On Fri, 2023-12-01 at 18:49 +0530, Ashutosh Bapat wrote:
On Thu, Nov 30, 2023 at 10:29 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2023-11-30 at 19:22 +0530, Ashutosh Bapat wrote:
May be attach the patch to hackers thread (this) as well?
If you want, sure. I thought it was good enough if the thread
is accessible via the commitfest app.The addition is long enough that it deserved to be outside of parentheses.
I think it's worth mentioning the exception but in a way that avoids
repeating what's mentioned in the last paragraph of just the previous
section. I don't have brilliant ideas about how to rephrase it.Maybe "Using ONLY to add or drop a constraint, other than PRIMARY and
UNIQUE, on only the partitioned table is supported as long as there
are no partitions. ...".
I agree that the parenthesis is too long. I shortened it in the attached
patch. Is that acceptable?
Yours,
Laurenz Albe
Attachments:
0001-Doc-Fix-omission-in-partitioning-limitations.V2.patchtext/x-patch; charset=UTF-8; name=0001-Doc-Fix-omission-in-partitioning-limitations.V2.patchDownload
From 5a0f480e8ebf26be8dd84ca76dd9217bb7eea860 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Mon, 4 Dec 2023 21:07:19 +0100
Subject: [PATCH] Doc: Fix omission in partitioning limitations
UNIQUE and PRIMARY KEY constraints can be created on ONLY the
partitioned table. We already had an example demonstrating that,
but forgot to mention it in the documentation of the limits of
partitioning.
Author: Laurenz Albe
Discussion: https://postgr.es/m/167299368731.659.16130012959616771853@wrigleys.postgresql.org
---
doc/src/sgml/ddl.sgml | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 4490e82aa5..922766ef1c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4341,7 +4341,9 @@ ALTER INDEX measurement_city_id_logdate_key
Using <literal>ONLY</literal> to add or drop a constraint on only
the partitioned table is supported as long as there are no
partitions. Once partitions exist, using <literal>ONLY</literal>
- will result in an error. Instead, constraints on the partitions
+ will result in an error (unless you use <literal>ONLY</literal> to
+ create <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>
+ constraints as shown above). Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent
table) dropped.
</para>
--
2.43.0
On Tue, Dec 5, 2023 at 1:40 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2023-12-01 at 18:49 +0530, Ashutosh Bapat wrote:
On Thu, Nov 30, 2023 at 10:29 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2023-11-30 at 19:22 +0530, Ashutosh Bapat wrote:
May be attach the patch to hackers thread (this) as well?
If you want, sure. I thought it was good enough if the thread
is accessible via the commitfest app.The addition is long enough that it deserved to be outside of parentheses.
I think it's worth mentioning the exception but in a way that avoids
repeating what's mentioned in the last paragraph of just the previous
section. I don't have brilliant ideas about how to rephrase it.Maybe "Using ONLY to add or drop a constraint, other than PRIMARY and
UNIQUE, on only the partitioned table is supported as long as there
are no partitions. ...".I agree that the parenthesis is too long. I shortened it in the attached
patch. Is that acceptable?
It's still longer than the actual sentence :). I am fine with it if
somebody else finds it acceptable.
--
Best Wishes,
Ashutosh Bapat
On Tue, Dec 5, 2023 at 3:57 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
On Tue, Dec 5, 2023 at 1:40 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2023-12-01 at 18:49 +0530, Ashutosh Bapat wrote:
On Thu, Nov 30, 2023 at 10:29 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2023-11-30 at 19:22 +0530, Ashutosh Bapat wrote:
May be attach the patch to hackers thread (this) as well?
If you want, sure. I thought it was good enough if the thread
is accessible via the commitfest app.The addition is long enough that it deserved to be outside of parentheses.
I think it's worth mentioning the exception but in a way that avoids
repeating what's mentioned in the last paragraph of just the previous
section. I don't have brilliant ideas about how to rephrase it.Maybe "Using ONLY to add or drop a constraint, other than PRIMARY and
UNIQUE, on only the partitioned table is supported as long as there
are no partitions. ...".I agree that the parenthesis is too long. I shortened it in the attached
patch. Is that acceptable?It's still longer than the actual sentence :). I am fine with it if
somebody else finds it acceptable.
It still reads a bit weird to me. How about the attached wording instead?
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
Attachments:
v3-Fix-omission-in-partitioning-limitation-documenhtation.patchtext/x-patch; charset=US-ASCII; name=v3-Fix-omission-in-partitioning-limitation-documenhtation.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 22d04006ad..01b1d82b0d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4348,7 +4348,9 @@ ALTER INDEX measurement_city_id_logdate_key
Using <literal>ONLY</literal> to add or drop a constraint on only
the partitioned table is supported as long as there are no
partitions. Once partitions exist, using <literal>ONLY</literal>
- will result in an error. Instead, constraints on the partitions
+ will result in an error for any constraints other than
+ <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>.
+ Instead, constraints on the partitions
themselves can be added and (if they are not present in the parent
table) dropped.
</para>
On Wed, 2024-01-10 at 13:41 +0100, Magnus Hagander wrote:
It still reads a bit weird to me. How about the attached wording instead?
Thanks! I am fine with your wording.
Yours,
Laurenz Albe
On Wed, Jan 10, 2024 at 10:38 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-01-10 at 13:41 +0100, Magnus Hagander wrote:
It still reads a bit weird to me. How about the attached wording instead?
Thanks! I am fine with your wording.
Works for me too.
--
Best Wishes,
Ashutosh Bapat
On Thu, Jan 11, 2024 at 11:24 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
On Wed, Jan 10, 2024 at 10:38 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-01-10 at 13:41 +0100, Magnus Hagander wrote:
It still reads a bit weird to me. How about the attached wording instead?
Thanks! I am fine with your wording.
Works for me too.
Thanks, applied and backpatched all the way.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/