Postgres 11 Insufficient columns in PRIMARY KEY constraint definition

On 2018-Dec-19, Joshua Muzaaya wrote:

DETAIL: PRIMARY KEY constraint on table lacks column "sdate" which is part
of the partition key. SQL state: 0A000

I have a table which i am trying to create with RANGE partitioning using
the timestamp column. But my primary doesnot need to have this timestamp
column, its another column.

Yeah, that won't work.

Why is postgres 11 asking me to add this
partition key in the primary key ?

Implementation restrictions. We may lift it in future releases, but
don't hold your breath.

The documentation lacks this or am missing something ?

It seems the docs are unclear on this ... failed edits. The PRIMARY KEY
part of it is clear; they say:

PRIMARY KEY constraints share the restrictions that UNIQUE constraints
have when placed on partitioned tables.

But under UNIQUE you find this:

When establishing a unique constraint for a multi-level partition
hierarchy, all the columns in the partition key of the target
partitioned table, as well as those of all its descendant partitioned
tables, must be included in the constraint definition.

in reality it doesn't matter than the hierarchy is multi-level or not --
the restriction applies to all partitioned setups.

I think this may be clearer:

When establishing a unique constraint on a partitioned table, all the
columns in the partition key of the partitioned table must be
included in the constraint definition. In case of a multi-level
partition hierarchy, this applies to the set of all columns used in
partition keys across the whole hierarchy.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Thanks alot Alvaro.
For my use case, should i fall back to INHERITANCE and trigger based
partitioning (
https://zaiste.net/table_inheritance_and_partitioning_with_postgresql/)
?
Changing my primary key to include the date column will not work for the
logic required. I also would not want to eliminate the unique key
constraints.
What do u advise me to do as i anticipate that the tables are going to grow
so large on quarterly basis ?

I could also use ROLL UP table but that would work if i could apply
triggers on views

On Wed, Dec 19, 2018 at 7:49 PM Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

On Thu, 20 Dec 2018 at 11:10, Joshua Muzaaya <joshmuza@gmail.com> wrote:

For my use case, should i fall back to INHERITANCE and trigger based partitioning (https://zaiste.net/table_inheritance_and_partitioning_with_postgresql/)
?
Changing my primary key to include the date column will not work for the logic required. I also would not want to eliminate the unique key constraints.
What do u advise me to do as i anticipate that the tables are going to grow so large on quarterly basis ?

Well, the purpose of adding the PRIMARY KEY must surely to be to
ensure the PK column values are unique over the entire partition
hierarchy. With inheritance you're only able to create a primary key
on each individual table, so how does that solve your problem of
ensuring no duplication over all partitions? You'd need to create
some sort of BEFORE INSERT/UPDATE triggers that check for duplicates
in each other partition before the insert/update is allowed. That's
not going to perform very well and will be a pain to maintain when you
add new partitions.

The missing feature Álvaro is talking about here is global indexes; a
single index that can store tuples from multiple relations. An
implementation of this detracts from one of the most useful things
about partitioning, that is, when you drop a partition it's about as
complex as a metadata update. When you have a global index you have to
remove or invalidate all indexed tuples belonging to the dropped
partition.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services