Table Partitioning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Table Partitioning

PG Bug reporting form
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/ddl-partitioning.html
Description:

With version 12 using declarative partitioning request that the column used
for patitioning is part of the PRIMARY KEY of the table as you can see in
the error message below.

ERROR:  insufficient columns in PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "ztest" lacks column "mtime" which
is part of the partition key.

If I've well understood, this was not the case in previous versions, but it
becomes mandatory in version 12 (and maybe 11 too). Such restriction is not
mentioned in the documentation for version 12 and would be suitable to be.

Best regards
Reply | Threaded
Open this post in threaded view
|

Re: Table Partitioning

Bruce Momjian
On Thu, Jul 30, 2020 at 01:33:29PM +0000, PG Doc comments form wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/ddl-partitioning.html
> Description:
>
> With version 12 using declarative partitioning request that the column used
> for patitioning is part of the PRIMARY KEY of the table as you can see in
> the error message below.
>
> ERROR:  insufficient columns in PRIMARY KEY constraint definition
> DETAIL:  PRIMARY KEY constraint on table "ztest" lacks column "mtime" which
> is part of the partition key.
>
> If I've well understood, this was not the case in previous versions, but it
> becomes mandatory in version 12 (and maybe 11 too). Such restriction is not
> mentioned in the documentation for version 12 and would be suitable to be.

Can you give us a reproducible test case?

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

Re: Table Partitioning

Tom Lane-2
Bruce Momjian <[hidden email]> writes:
> On Thu, Jul 30, 2020 at 01:33:29PM +0000, PG Doc comments form wrote:
>> If I've well understood, this was not the case in previous versions, but it
>> becomes mandatory in version 12 (and maybe 11 too). Such restriction is not
>> mentioned in the documentation for version 12 and would be suitable to be.

> Can you give us a reproducible test case?

I don't think this is correct at all.  The facility for pkeys or unique
constraints on partitioned tables simply didn't exist before v11:

$ psql
psql (10.13)
Type "help" for help.

postgres=# create table p(f1 int primary key, f2 int) partition by list(f2);
ERROR:  primary key constraints are not supported on partitioned tables
LINE 1: create table p(f1 int primary key, f2 int) partition by list...
                              ^
postgres=# create table p(f1 int unique, f2 int) partition by list(f2);
ERROR:  unique constraints are not supported on partitioned tables
LINE 1: create table p(f1 int unique, f2 int) partition by list(f2);
                              ^

so the fact that they're restricted in this way as of v11 and up
does not represent any loss of functionality.

                        regards, tom lane