pg12 - foreign key to partitions

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pg12 - foreign key to partitions
Date: 2019-09-12 14:53:37
Message-ID: CA+t6e1k3fA5rwzFmFdszjhSFbyLGFBOGLTpsN=JLmrjDA27YfQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hey,
I was playing with the new features that were added to pg12 and I wanted to
ask something that looked weird for me.

Lets assume I have the following non partitions structure :

Product(id int PK,vendor int references Vendor(id),price int)
ProductPic(int picId PK,product int references product(id) )
Vendor(id int PK,name text)
.... more tables that has references to the Product(id).

Now, I successfully converted the current Product table into a hash
partition based on the vendor column. Now when I tried to recreate the fk
column ProductPic(product) - I'm getting the following error:
ERROR: there is no unique constraint matching given keys for referenced
table "Product"

during the conversion I had to create a PK on (id,vendor) because of the
partitioning. So in order to recreate all the references to the product
table I need to add to all those tables the vendor column also ? Isnt there
any other way to solve it ?

Browse pgsql-admin by date

  From Date Subject
Next Message Dhandapani Shanmugam 2019-09-12 15:35:42 pgdump slowness
Previous Message Luca Ferrari 2019-09-09 06:53:59 Re: Streaming replication issue