Message-ID: <c8345f6323d6ea9c22e0f44125fd9815081c4c80.camel@cybertec.at>
Subject: Re: Postgres Partitions Limitations (5.11.2.3)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: navbarry@gmail.com, pgsql-docs@lists.postgresql.org
Date: Mon, 09 Jan 2023 16:40:10 +0100
In-Reply-To: <167299368731.659.16130012959616771853@wrigleys.postgresql.org>
References: <167299368731.659.16130012959616771853@wrigleys.postgresql.org>
Content-Type: multipart/mixed; boundary="=-q1oXsWShCi5C8MrO8FkB"
User-Agent: Evolution 3.46.2 (3.46.2-1.fc37) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/c8345f6323d6ea9c22e0f44125fd9815081c4c80.camel%40cybertec.at>
Precedence: bulk

--=-q1oXsWShCi5C8MrO8FkB
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, 2023-01-06 at 08:28 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>=20
> Page: https://www.postgresql.org/docs/15/ddl-partitioning.html
> Description:
>=20
> Link:
> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITI=
ONING-DECLARATIVE
>=20
> "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, usin=
g
> 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:
>=20
> "
> This technique can be used with UNIQUE and PRIMARY KEY constraints too; t=
he
> indexes are created implicitly when the constraint is created. Example:
>=20
> ```ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
>=20
> ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
> ALTER INDEX measurement_city_id_logdate_key
> =C2=A0=C2=A0=C2=A0 ATTACH PARTITION measurement_y2006m02_city_id_logdate_=
key;
> ...
> ```
> "
>=20
> I might be misinterpreting something. Sorry if that's the case!=20

No, that is actually an omission in the documentation.

The attached patch tries to improve that.

Yours,
Laurenz Albe

--=-q1oXsWShCi5C8MrO8FkB
Content-Disposition: attachment;
	filename*0=0001-Fix-omission-in-partitioning-limitation-documentatio.pat;
	filename*1=ch
Content-Transfer-Encoding: base64
Content-Type: text/x-patch;
	name="0001-Fix-omission-in-partitioning-limitation-documentatio.patch";
	charset="UTF-8"

RnJvbSBlY2RjZTc0MDU4NmUzM2VlYjM5NGQ0NzU2NGIxMGY4MTM4OTZmZjExIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5h
dD4KRGF0ZTogTW9uLCA5IEphbiAyMDIzIDE2OjM4OjU4ICswMTAwClN1YmplY3Q6IFtQQVRDSF0g
Rml4IG9taXNzaW9uIGluIHBhcnRpdGlvbmluZyBsaW1pdGF0aW9uIGRvY3VtZW50YXRpb24KClVO
SVFVRSBhbmQgUFJJTUFSWSBLRVkgY29uc3RyYWludHMgY2FuIGJlIGNyZWF0ZWQgb24gT05MWSB0
aGUKcGFydGl0aW9uZWQgdGFibGUuICBXZSBhbHJlYWR5IGhhZCBhbiBleGFtcGxlIGRlbW9uc3Ry
YXRpbmcgdGhhdCwKYnV0IGZvcmdvdCB0byBtZW50aW9uIGl0IGluIHRoZSBkb2N1bWVudGF0aW9u
IG9mIHRoZSBsaW1pdHMgb2YKcGFydGl0aW9uaW5nLgoKQXV0aG9yOiBMYXVyZW56IEFsYmUKRGlz
Y3Vzc2lvbjogaHR0cHM6Ly9wb3N0Z3IuZXMvbS8xNjcyOTkzNjg3MzEuNjU5LjE2MTMwMDEyOTU5
NjE2NzcxODUzQHdyaWdsZXlzLnBvc3RncmVzcWwub3JnCi0tLQogZG9jL3NyYy9zZ21sL2RkbC5z
Z21sIHwgNSArKysrLQogMSBmaWxlIGNoYW5nZWQsIDQgaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlv
bigtKQoKZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9kZGwuc2dtbCBiL2RvYy9zcmMvc2dtbC9k
ZGwuc2dtbAppbmRleCA2ZTkyYmJkZGQyLi5iNGE3NWY5YzhmIDEwMDY0NAotLS0gYS9kb2Mvc3Jj
L3NnbWwvZGRsLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL2RkbC5zZ21sCkBAIC00MjgyLDcgKzQy
ODIsMTAgQEAgQUxURVIgSU5ERVggbWVhc3VyZW1lbnRfY2l0eV9pZF9sb2dkYXRlX2tleQogICAg
ICAgICBVc2luZyA8bGl0ZXJhbD5PTkxZPC9saXRlcmFsPiB0byBhZGQgb3IgZHJvcCBhIGNvbnN0
cmFpbnQgb24gb25seQogICAgICAgICB0aGUgcGFydGl0aW9uZWQgdGFibGUgaXMgc3VwcG9ydGVk
IGFzIGxvbmcgYXMgdGhlcmUgYXJlIG5vCiAgICAgICAgIHBhcnRpdGlvbnMuICBPbmNlIHBhcnRp
dGlvbnMgZXhpc3QsIHVzaW5nIDxsaXRlcmFsPk9OTFk8L2xpdGVyYWw+Ci0gICAgICAgIHdpbGwg
cmVzdWx0IGluIGFuIGVycm9yLiAgSW5zdGVhZCwgY29uc3RyYWludHMgb24gdGhlIHBhcnRpdGlv
bnMKKyAgICAgICAgd2lsbCByZXN1bHQgaW4gYW4gZXJyb3IgKHRoZSBleGNlcHRpb24gdG8gdGhp
cyBhcmUKKyAgICAgICAgPGxpdGVyYWw+VU5JUVVFPC9saXRlcmFsPiBhbmQgPGxpdGVyYWw+UFJJ
TUFSWSBLRVk8L2xpdGVyYWw+CisgICAgICAgIGNvbnN0cmFpbnRzLCB3aGljaCB3aWxsIGJlIGNy
ZWF0ZWQgd2l0aCBhbiBpbnZhbGlkIGluZGV4LCBhcyBzaG93biBpbgorICAgICAgICB0aGUgZXhh
bXBsZSBhYm92ZSkuICBJbnN0ZWFkLCBjb25zdHJhaW50cyBvbiB0aGUgcGFydGl0aW9ucwogICAg
ICAgICB0aGVtc2VsdmVzIGNhbiBiZSBhZGRlZCBhbmQgKGlmIHRoZXkgYXJlIG5vdCBwcmVzZW50
IGluIHRoZSBwYXJlbnQKICAgICAgICAgdGFibGUpIGRyb3BwZWQuCiAgICAgICAgPC9wYXJhPgot
LSAKMi4zOS4wCgo=


--=-q1oXsWShCi5C8MrO8FkB--