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