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

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(at)gmail(dot)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(dot)albe(at)cybertec(dot)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

On Thu, Nov 30, 2023 at 10:29 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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(dot)albe(at)cybertec(dot)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

On Tue, Dec 5, 2023 at 1:40 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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(dot)albe(at)cybertec(dot)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(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Tue, Dec 5, 2023 at 1:40 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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(dot)albe(at)cybertec(dot)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/

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(dot)albe(at)cybertec(dot)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(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Wed, Jan 10, 2024 at 10:38 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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/

On Thu, 2024-01-11 at 14:44 +0100, Magnus Hagander wrote:
> Thanks, applied and backpatched all the way.

Thanks for taking care of that!

Yours,
Laurenz Albe