Re: Table partition with primary key in 11.3

On Thu, 6 Jun 2019 at 18:03, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> On 2019-Jun-06, Alex V. wrote:
>
> > I think that your position about primary keys in partitional tables is
> > not right.
> >
> > If we see regular table, one-field primary key is cross-table unique.
> > In partitional tables for users view we MUST also seen unique
> > one-field primary key because this is user requirement and another
> > keys can destroy logic between regular and partitional tables and
> > functionality of partitioning becomes useless.
> > For administrators of table we not-MAY, but MUST see one-field unique
> > primary key in cross-table realisation.
> > All another realizations are plugging holes in a hurry and non-logical
> > at global review of engine.
>
> If you are saying that you think that Postgres should support primary
> keys that don't necessarily overlap partition keys, then I agree with
> you. Please send a patch to implement that capability.
>

The point is that unique indexes that overlap partition keys are rather
cheap because they can be made from a set of local indexes.

A unique index that doesn't overlap partition keys would be
* unfeasibly huge
* likely to hit the 32TB limit on relations
* unable to cope with dropping partitions

It sounds like you want it, cos its just a line of DDL, but in practice
your colleagues wouldn't want those things.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Solutions for the Enterprise