Postgres Pro
Downloads
Home   >   mailing lists

Thread: On partitioning, PKs and FKs

On partitioning, PKs and FKs

From
Wiwwo Staff
Date:
Hi!
I have a big table bigTable which I partitioned by hash on field columnX, by creating bigTable_0, bigTable_1 etc.
Since I need a PK on bigTable.id, and table is not partitioned by id, and columnX is not unique, i added PK on bigTable_0.id, bigTable_1.id etc. So far, so good.

Now I have anotherTable, which has column bigTable_id referencing bigTable.id.
Creating FK anotherTable__bigTable_FK on anotherTable.bigTable_id -you guess :D- fails with
[42830] ERROR: there is no unique constraint matching given keys for referenced table "bigTable"
Also creating a FK pointing to one of the hash tables bigTable_0.id, bigTable_1.id etc. also fails with
ERROR:  23503: insert or update on table "anotherTable" violates foreign key constraint "anotherTable__bigTable_FK".

Do I have a way out? :)

Re: On partitioning, PKs and FKs

From
Ron
Date:
On 7/8/21 3:42 AM, Wiwwo Staff wrote:
Hi!
I have a big table bigTable which I partitioned by hash on field columnX, by creating bigTable_0, bigTable_1 etc.
Since I need a PK on bigTable.id, and table is not partitioned by id, and columnX is not unique, i added PK on bigTable_0.id, bigTable_1.id etc. So far, so good.

Now I have anotherTable, which has column bigTable_id referencing bigTable.id.
Creating FK anotherTable__bigTable_FK on anotherTable.bigTable_id -you guess :D- fails with
[42830] ERROR: there is no unique constraint matching given keys for referenced table "bigTable"
Also creating a FK pointing to one of the hash tables bigTable_0.id, bigTable_1.id etc. also fails with
ERROR:  23503: insert or update on table "anotherTable" violates foreign key constraint "anotherTable__bigTable_FK".

Do I have a way out? :)

https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

Section "5.11.2.3. Limitations" says, "Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns."

Thus, the bigTable PK must be on id, columnX,  (No, I don't like it either.)

--
Angular momentum makes the world go 'round.

Re: On partitioning, PKs and FKs

From
Michael Lewis
Date:
Why are you using hash partitioning?

Re: On partitioning, PKs and FKs

From
Wiwwo Staff
Date:
Big big variability on partitioned column, which is filtered (where condition) in (almost) all queries.

On Thu, 8 Jul 2021 at 14:23, Michael Lewis <mlewis@entrata.com> wrote:
Why are you using hash partitioning?

Re: On partitioning, PKs and FKs

From
Ron
Date:
The docs are pretty clear that all constraints must have the partition key as part of the key, and PK is most certainly a constraint.

I welcome a counter-example.

On 7/8/21 10:23 AM, Alban Hertroys wrote:
On 2021-07-08 13:30, Ron wrote:
> Thus, the bigTable PK must be on id, columnX, (No, I don't like it
> either.)

That's not entirely true. You can keep the PK on id if you additionally
create a unique constraint on (id, columnX).

That way, you can at least be certain that the uniqueness of the PK
remains in-tact, even if that is a surrogate key that has nothing to do
with the uniqueness of the actual records.

Regards,

Alban Hertroys.


Alban Hertroys    

 

  

Apollo Tyres (NL) B.V.
Ir. E.L.C. Schiffstraat 370, 7547 RD Enschede, The Netherlands
+31 (0)53 4888 776

alban.hertroys@apollotyres.com
Chamber of Commerce number: 34223268

CIN: L25111KL1972PLC002449

Registered Office: Apollo Tyres Ltd, 3rd Floor, Areekal Mansion, Panampilly Nagar, Kochi  682036, India



Disclaimer:

The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Tyres and its subsidiaries rule out any and every liability resulting from this or any other electronic transmiss

--
Angular momentum makes the world go 'round.

Re: On partitioning, PKs and FKs

From
Wiwwo Staff
Date:
On Thu, 8 Jul 2021 at 21:42, Alban Hertroys <alban.hertroys@apollotyres.com> wrote:
On 2021-07-08 13:30, Ron wrote:
> Thus, the bigTable PK must be on id, columnX, (No, I don't like it
> either.)

That's not entirely true. You can keep the PK on id if you additionally
create a unique constraint on (id, columnX).
 
Uhm, but that means that i have to partition by id and columnX, which is not what I really want...
=> create table bigtable
(
    id      numeric primary key,
    columnX varchar(5),
    constraint test_unique unique (id, columnX)
) partition by hash (columnx);
ERROR:  0A000: unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "bigtable" lacks column "columnx" which is part of the partition key.
LOCATION:  DefineIndex, indexcmds.c:946
Time: 146.254 ms

Also
) partition by hash (id, columnx);
ERROR:  0A000: unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "bigtable" lacks column "columnx" which is part of the partition key.
LOCATION:  DefineIndex, indexcmds.c:946
Time: 150.646 ms

and
) partition by hash (id);
CREATE TABLE
Time: 152.780 ms
which is not what I want...

Re: On partitioning, PKs and FKs

From
"David G. Johnston"
Date:
On Thursday, July 8, 2021, Wiwwo Staff <wiwwo@wiwwo.com> wrote:
On Thu, 8 Jul 2021 at 21:42, Alban Hertroys <alban.hertroys@apollotyres.com> wrote:
On 2021-07-08 13:30, Ron wrote:
> Thus, the bigTable PK must be on id, columnX, (No, I don't like it
> either.)

That's not entirely true. You can keep the PK on id if you additionally
create a unique constraint on (id, columnX).
 
Uhm, but that means that i have to partition by id and columnX, which is not what I really want...

Those examples show that Alban’s comment that you can keep the PK on id is incorrect.

All you can do is ensure that a given non-partition value is unique on any given partition.  You cannot setup a constraint that definitionally requires the entire partition tree to coordinate and ensure none of the partitions have duplicates among them.

David J.