Re: Declarative partitioning - another take

On Thu, Nov 3, 2016 at 7:46 AM,  <alvherre@alvh.no-ip.org> wrote:
> El 2016-10-28 07:53, Amit Langote escribió:
>> @@ -6267,6 +6416,12 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
>> Relation rel,
>>          * Validity checks (permission checks wait till we have the column
>>          * numbers)
>>          */
>> +       if (pkrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
>> +               ereport(ERROR,
>> +                               (errcode(ERRCODE_WRONG_OBJECT_TYPE),
>> +                                errmsg("cannot reference relation
>> \"%s\"", RelationGetRelationName(pkrel)),
>> +                                errdetail("Referencing partitioned tables
>> in foreign key constraints is not supported.")));
>
> Is there a plan for fixing this particular limitation?  It's a pretty
> serious problem for users,
> and the suggested workaround (to create a separate non-partitioned table
> which carries only the PK
> columns which is updated by triggers, and direct the FKs to it instead of to
> the partitioned table)
> is not only a very ugly one, but also very slow.

If you have two compatibly partitioned tables, and the foreign key
matches the partitioning keys, you could implement a foreign key
between the two tables as a foreign key between each pair of matching
partitions.  Otherwise, isn't the only way to handle this a global
index?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company