Обсуждение: Postgres 11 Insufficient columns in PRIMARY KEY constraint definition
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. Why is postgres 11 asking me to add this partition key in the primary key ?
The documentation lacks this or am missing something ?
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 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
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 eliminatethe 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