Re: docs about FKs referencing partitioned tables - Mailing list pgsql-hackers

On Wed, May 22, 2019 at 8:06 PM Michael Paquier <michael@paquier.xyz> wrote:
> Looking closer, you are adding that:
> +     <listitem>
> +      <para>
> +       While primary keys are supported on tables using inheritance
> +       for partitioning, foreign keys referencing these tables are not
> +       supported.  (Foreign key references from an inherited table to
> +       some other table are supported.)
> +      </para>
> +     </listitem>
>
> However that's just fine:
> =# create table aa (a int primary key);
> CREATE TABLE
> =# create table aa_child (a int primary key, inherits aa, foreign key
> (a) references aa);
> CREATE TABLE
> =# create table aa_grandchild (a int primary key, inherits aa_child,
> foreign key (a) references aa_child);
> CREATE TABLE

Postgres will let you define the FK, but it doesn't work in a meaningful way:

paul=# create table t1 (id int primary key, foo text);
CREATE TABLE
paul=# create table t2 (bar text) inherits (t1);
CREATE TABLE
paul=# insert into t2 values (1, 'f', 'b');
INSERT 0 1
paul=# select * from t1;
 id | foo
----+-----
  1 | f
(1 row)

paul=# create table ch (id int, t_id int references t1 (id));
CREATE TABLE
paul=# insert into ch values (1, 1);
ERROR:  insert or update on table "ch" violates foreign key constraint
"ch_t_id_fkey"
DETAIL:  Key (t_id)=(1) is not present in table "t1".

The section in the docs (5.10) just before the one I changed has
similar warnings:

> Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.

and

> A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key
constraintsonly apply to single tables, not to their inheritance children.
 

> The paragraph you are removing from 5.11.2.3 (limitations of
> declarative partitioning) only applies to partitioned tables, not to
> plain tables.  And there is no such thing for paritioning based on
> inheritance, so we should just remove one paragraph, and not add the
> extra one, no?

I moved the paragraph to a section describing inheritance as an
alternative partitioning solution to declarative partitions. Since
using inheritance to partition a table requires giving up foreign
keys, it seems worthwhile to include that among the other caveats. (It
wasn't necessary to include it before because declarative partitions
had the same drawback, and it was already expressed in the paragraph I
took out.) In my opinion mentioning this limitation would be helpful
to people.

Perhaps the wording is too strong though:

> +       . . . foreign keys referencing these tables are not
> +       supported. . . .

I was trying to make a minimal change by keeping most of the original
wording, but I agree that different language would be more accurate.
What do you think of something like this?:

+     <listitem>
+      <para>
+       While foreign keys may be defined that reference a parent
+       table, they will not see records from its child tables. Since
+       the parent table is typically empty, adding any record (with a
+       non-null foreign key) to the referencing table will raise an error.
+      </para>
+     </listitem>

Paul