Date: Tue, 9 Jul 2019 11:58:56 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Bruce Momjian <bruce@momjian.us>
Cc: phil.bayer@gmail.com, pgsql-docs@lists.postgresql.org,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Postgres 11: Table Partitioning and Primary Keys
Message-ID: <20190709025856.GC1345@paquier.xyz>
References: <156236160709.1192.4498528196556144085@wrigleys.postgresql.org>
 <20190709023737.vhrd4pxe4hgnd3ks@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="fOHHtNG4YXGJ0yqR"
Content-Disposition: inline
In-Reply-To: <20190709023737.vhrd4pxe4hgnd3ks@momjian.us>
Precedence: bulk


--fOHHtNG4YXGJ0yqR
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Mon, Jul 08, 2019 at 10:37:37PM -0400, Bruce Momjian wrote:
> On Fri, Jul  5, 2019 at 09:20:07PM +0000, PG Doc comments form wrote:
>> In the documentation for Postgres 11 table partitioning, there is no men=
tion
>> of the requirement that the Primary Key of a partitioned table must cont=
ain
>> the partition key.
>> In fact the documentation on primary keys is so light that I am not even
>> 100% sure the above is correct.  If the following table is not possible =
in
>> Postgres 11, the documentation should find some way to make that clear. =
=20
>>=20
>> I believe this should be documented in section "5.10.2.3. Limitations"
>=20
> Can someone comment on this?  CC to hackers.

Yep, that's the case:
=3D# CREATE TABLE parent_tab (id int, id2 int primary key)
     PARTITION BY RANGE (id);
ERROR:  0A000: insufficient columns in PRIMARY KEY constraint
definition
DETAIL:  PRIMARY KEY constraint on table "parent_tab" lacks column
"id" which is part of the partition key.
LOCATION:  DefineIndex, indexcmds.c:894

I agree with the report here that adding one sentence to 5.10.2.3
which is for the limitations of declarative partitioning would be a
good idea.  We don't mention the limitation in CREATE TABLE either
(which would be rather incorrect IMO).

Attached is an idea of patch for the documentation, using this
wording:
+     <listitem>
+      <para>
+       When defining a primary key on a partitioned table, the primary
+       key column must be included in the partition key.
+      </para>
+     </listitem>
If somebody has any better idea for that paragraph, please feel free.
--
Michael

--fOHHtNG4YXGJ0yqR
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl0kAvAACgkQnvQgOdby
QH1/EA//SRf94LhSzzavfTNyp2Nn+lHv/fkRK0Mzrxo1ld2w0Ow0c9tde1hk5mPT
J6zu2LDlOq4U/NkxaTp6RNvs+uh/8x9/TWBRW3rVUqgFC+Vmqwbt6YLvrkkE23E6
fNc3zWcdXx/YeNZdWyqptEMuu/FrmGUBBIC+Xl/Zq/RC/NCmgpQhjSLKP7uy69sR
OK9OaMoSZB9PumpNG3MVt7tEes5asuPDVZl0zcPk4ga7cImzxb5Wn+5UDqNl2+wL
qVnXKbqLnHdhQ2vbtpKeYELRuUvmUqy22UdRBD/1a44gYNybp2qQ1dD4xIRynaha
WiWCUu06zYgYd/H8k9TFJelrwAg1fam/RP1h7jN6pVF0FDgO2awlp0iaZOgacD/i
mmGh/653d58ighfzjqEMQLfJWP4NUHk1rTrBAeN/UCuRm1y/rUfUQYu4kQ0OGlHj
2EwpRr0+Y7x+fr3Whjf4XhdTB8aLO2m8ewRnrF0I160ecwcz6UTiqGCQ3HHOtwKN
6xju0GTSpQN9yarL8F3YVwAGk8Pu2zmscu21kVdqHLNtSVc3gHmR/vwDQjlgv7kz
UH6vSsAv4/dNNdGpAVWJqHop0qQEVcL8GL733WofmOk+pheyxgZsbKutTozC7jVI
aUmqQTYMNZ0NcPHrY1P+Q1uq9AjXw1NgdoYQXSeVB++by3r7ay8=
=fHOU
-----END PGP SIGNATURE-----

--fOHHtNG4YXGJ0yqR--