From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Scalia <jayknowsunix@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Creating a partition table
Date: Fri, 30 Oct 2020 14:09:02 -0400
Message-ID: <101520.1604081342@sss.pgh.pa.us> (raw)
In-Reply-To: <D8D76052-3A69-4E31-8E5C-EB965FE6D53A@gmail.com>
References: <98825.1604077633@sss.pgh.pa.us>
<D8D76052-3A69-4E31-8E5C-EB965FE6D53A@gmail.com>
John Scalia <jayknowsunix@gmail.com> writes:
> Ok I tried what Laurent had suggested and now the error is:
> Insufficient columns in PRIMARY KEY constraint definition.
> Now, the attribute the devs want is not a key value in the original table, but if it were that wouldn’t make a great deal of sense for a partition. So, now why did it gripe?
FWIW, we recently rephrased that message as "unique constraint on
partitioned table must include all partitioning columns", because
people were finding it confusing.
The reason for the restriction is that a partitioned index is really
just a collection of separate indexes on the individual leaf partitions
(just as the partitioned table is a collection of plain tables).
So it can't enforce uniqueness across partitions unless such uniqueness
is guaranteed by the partitioning scheme.
Yeah, this is an implementation restriction, but I don't foresee it
ever going away. If the indexes weren't independent then you'd lose
the scalability benefits that partitioning is meant to provide.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jayknowsunix@gmail.com
Subject: Re: Creating a partition table
In-Reply-To: <101520.1604081342@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox