Обсуждение: Foreign key against a partitioned table

Поиск
Список
Период
Сортировка

Foreign key against a partitioned table

От
Craig James
Дата:
How do you create a foreign key that references a partitioned table?

I'm splitting a large table "molecules" into 20 partitions, which also has an associated "molecular_properties" table. It looks something like this (pseudo-code):

create table molecules(molecule_id    integer primary key,
                       molecule_data  text,
                       p              integer);

foreach $p (0..19) {
    create table molecules_$p (check(p = $p)) inherits (molecules);
}

create table molecular_properties(molprops_id       integer primary key,
                                  molecule_id       integer,
                                  molecular_weight  numeric(8,3));
alter table molecular_properties
  add constraint fk_molecular_properties
  foreign key(molecule_id)
  references molecules(molecule_id);

(NB: There is no natural way to partition molecules, so the value for p is a random number. There is a good reason for partitioning that's not relevant to my question...)

When I try to insert something into the molecular_properties table it fails:

insert or update on table "molecular_properties" violates foreign key constraint "fk_molecular_properties"
DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".

This surprised me. Obviously ID isn't in the "molecules" parent table, but I guessed that the foreign key would work anyway since the parent table is supposed to behave as though it includes all of the child tables.

So how do you create a foreign key on a partitioned table?

I suppose I could partition the molecular_properties table, but that would add unnecessary complication to the schema for no reason other than the "on delete cascade" feature.

The only other thing I can think of is a delete trigger on each of the partition child tables. That would work, but it's a nuisance.

Thanks,
Craig

Re: Foreign key against a partitioned table

От
Igor Neyman
Дата:

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Craig James
Sent: Tuesday, August 23, 2016 4:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Foreign key against a partitioned table

 

How do you create a foreign key that references a partitioned table?

 

I'm splitting a large table "molecules" into 20 partitions, which also has an associated "molecular_properties" table. It looks something like this (pseudo-code):

 

create table molecules(molecule_id    integer primary key,

                       molecule_data  text,

                       p              integer);

 

foreach $p (0..19) {

    create table molecules_$p (check(p = $p)) inherits (molecules);

}

 

create table molecular_properties(molprops_id       integer primary key,

                                  molecule_id       integer,

                                  molecular_weight  numeric(8,3));

alter table molecular_properties

  add constraint fk_molecular_properties

  foreign key(molecule_id)

  references molecules(molecule_id);

 

(NB: There is no natural way to partition molecules, so the value for p is a random number. There is a good reason for partitioning that's not relevant to my question...)

 

When I try to insert something into the molecular_properties table it fails:

 

insert or update on table "molecular_properties" violates foreign key constraint "fk_molecular_properties"

DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".

 

This surprised me. Obviously ID isn't in the "molecules" parent table, but I guessed that the foreign key would work anyway since the parent table is supposed to behave as though it includes all of the child tables.

 

So how do you create a foreign key on a partitioned table?

 

I suppose I could partition the molecular_properties table, but that would add unnecessary complication to the schema for no reason other than the "on delete cascade" feature.

 

The only other thing I can think of is a delete trigger on each of the partition child tables. That would work, but it's a nuisance.

 

Thanks,

Craig

 

 

You can’t.

Only through triggers as you suggested.

 

Regards,

Igor

Re: Foreign key against a partitioned table

От
Adam Brusselback
Дата:
I have wondered if there were any plans to enhance fkey support for partitioned tables now that more work is being done on partitioning (I know there has been a large thread on declarative partitioning on hackers, though I haven't followed it too closely).

Foreign keys are all done through triggers on the backend anyways, it does seem totally possible to have it work for partitioned tables if the code is aware that a table is partitioned and it needs to look in all inherited tables as well as the one specified.

Re: Foreign key against a partitioned table

От
Adrian Klaver
Дата:
On 08/23/2016 01:00 PM, Craig James wrote:
> How do you create a foreign key that references a partitioned table?
>
> I'm splitting a large table "molecules" into 20 partitions, which also
> has an associated "molecular_properties" table. It looks something like
> this (pseudo-code):
>
>     create table molecules(molecule_id    integer primary key,
>                            molecule_data  text,
>                            p              integer);
>
>     foreach $p (0..19) {
>         create table molecules_$p (check(p = $p)) inherits (molecules);
>     }
>
>     create table molecular_properties(molprops_id       integer primary key,
>                                       molecule_id       integer,
>                                       molecular_weight  numeric(8,3));
>     alter table molecular_properties
>       add constraint fk_molecular_properties
>       foreign key(molecule_id)
>       references molecules(molecule_id);
>
>
> (NB: There is no natural way to partition molecules, so the value for p
> is a random number. There is a good reason for partitioning that's not
> relevant to my question...)
>
> When I try to insert something into the molecular_properties table it fails:
>
>     insert or update on table "molecular_properties" violates foreign
>     key constraint "fk_molecular_properties"
>     DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".
>
>
> This surprised me. Obviously ID isn't in the "molecules" parent table,
> but I guessed that the foreign key would work anyway since the parent
> table is supposed to behave as though it includes all of the child tables.

I would say it is because of this:

https://www.postgresql.org/docs/9.5/static/sql-createtable.html

" Notes
...

Unique constraints and primary keys are not inherited in the current
implementation. This makes the combination of inheritance and unique
constraints rather dysfunctional.
...
"

>
> So how do you create a foreign key on a partitioned table?
>
> I suppose I could partition the molecular_properties table, but that
> would add unnecessary complication to the schema for no reason other
> than the "on delete cascade" feature.
>
> The only other thing I can think of is a delete trigger on each of the
> partition child tables. That would work, but it's a nuisance.
>
> Thanks,
> Craig


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Foreign key against a partitioned table

От
Craig James
Дата:


On Tue, Aug 23, 2016 at 1:07 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Craig James
Sent: Tuesday, August 23, 2016 4:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Foreign key against a partitioned table

 

How do you create a foreign key that references a partitioned table?

 

I'm splitting a large table "molecules" into 20 partitions, which also has an associated "molecular_properties" table. It looks something like this (pseudo-code):

 

create table molecules(molecule_id    integer primary key,

                       molecule_data  text,

                       p              integer);

 

foreach $p (0..19) {

    create table molecules_$p (check(p = $p)) inherits (molecules);

}

 

create table molecular_properties(molprops_id       integer primary key,

                                  molecule_id       integer,

                                  molecular_weight  numeric(8,3));

alter table molecular_properties

  add constraint fk_molecular_properties

  foreign key(molecule_id)

  references molecules(molecule_id);

 

(NB: There is no natural way to partition molecules, so the value for p is a random number. There is a good reason for partitioning that's not relevant to my question...)

 

When I try to insert something into the molecular_properties table it fails:

 

insert or update on table "molecular_properties" violates foreign key constraint "fk_molecular_properties"

DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".

 

This surprised me. Obviously ID isn't in the "molecules" parent table, but I guessed that the foreign key would work anyway since the parent table is supposed to behave as though it includes all of the child tables.

 

So how do you create a foreign key on a partitioned table?

 

I suppose I could partition the molecular_properties table, but that would add unnecessary complication to the schema for no reason other than the "on delete cascade" feature.

 

The only other thing I can think of is a delete trigger on each of the partition child tables. That would work, but it's a nuisance.

 

Thanks,

Craig

 

 

You can’t.

Only through triggers as you suggested.


OK thanks. Triggers it is.

Craig
 

 

Regards,

Igor




--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.