Re: Foreign keys and partitioned tables

Peter Eisentraut wrote:
> On 3/29/18 23:19, Alvaro Herrera wrote:
> > 0003 is the matter of interest.  This is essentially the same code I
> > posted earlier, rebased to the committed row triggers patch, with a few
> > minor bug fixes and some changes in the regression tests to try and make
> > them more comprehensive, including leaving a partitioned table with an
> > FK to test that the whole pg_dump thing works via the pg_upgrade test.
> 
> I've only read the tests so far.  The functionality appears to work
> correctly.  It's a bit strange how the tests are split up between
> alter_table.sql and foreign_key.sql, especially since the latter also
> contains ALTER TABLE checks and vice versa.

Yeah, I started by putting what I thought was going to be just ALTER
TABLE in that test, then moved to the other file and added what I
thought were more complete tests there and failed to move stuff to
alter_table.  Honestly, I think these should mostly all belong in
foreign_key, but of course the line is pretty blurry as to what to put
in which file.

> Some tests are a bit redundant, e.g., this in alter_table.sql:
> 
> +-- these fail:
> +INSERT INTO at_partitioned VALUES (1000, 42);
> +ERROR:  insert or update on table "at_partitioned_0" violates foreign
> key constraint "at_partitioned_reg1_col1_fkey"
> +DETAIL:  Key (reg1_col1)=(42) is not present in table "at_regular1".
> 
> and
> 
> +INSERT INTO at_partitioned VALUES (5000, 42);
> +ERROR:  insert or update on table "at_partitioned_0" violates foreign
> key constraint "at_partitioned_reg1_col1_fkey"
> +DETAIL:  Key (reg1_col1)=(42) is not present in table "at_regular1".

Oh, right.  I had some of these to support the case of a FK pointing to
a partitioned PK, but then deleted the other partitioned table that this
referred to, so the test looks kinda silly without the stuff that was
previously interspersed there.

I think I'll remove everything from alter_table and just add what's
missing to foreign_key.

> There are no documentation changes.  The foreign key section in CREATE
> TABLE does not contain anything about partitioned tables, which is
> probably an existing omission, but it might be good to fix this up now.

Good catch.  I propose this in the PARTITIONED BY section:

      <para>
-      Partitioned tables do not support <literal>EXCLUDE</literal> or
-      <literal>FOREIGN KEY</literal> constraints; however, you can define
-      these constraints on individual partitions.
+      Partitioned tables do not support <literal>EXCLUDE</literal> constraints;
+      however, you can define these constraints on individual partitions.
+      Also, while it's possible to define <literal>PRIMARY KEY</literal>
+      constraints on partitioned tables, it is not supported to create foreign
+      keys cannot that reference them.  This restriction will be lifted in a
+      future release.
      </para>

I propose this under the REFERENCES clause:

      <para>
       These clauses specify a foreign key constraint, which requires
       that a group of one or more columns of the new table must only
       contain values that match values in the referenced
       column(s) of some row of the referenced table.  If the <replaceable
       class="parameter">refcolumn</replaceable> list is omitted, the
       primary key of the <replaceable class="parameter">reftable</replaceable>
       is used.  The referenced columns must be the columns of a non-deferrable
       unique or primary key constraint in the referenced table.  The user
       must have <literal>REFERENCES</literal> permission on the referenced table
       (either the whole table, or the specific referenced columns).
       Note that foreign key constraints cannot be defined between temporary
-      tables and permanent tables.
+      tables and permanent tables.  Also note that while it is permitted to
+      define a foreign key on a partitioned table, declaring a foreign key
+      that references a partitioned table is not allowed.
      <para>

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services