Re: Declarative partitioning - another take

On 2016/11/04 0:49, Robert Haas wrote:
> On Thu, Nov 3, 2016 at 7:46 AM, <alvherre(at)alvh(dot)no-ip(dot)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?

I am assuming you don't mean a global index (on partitioned tables) as in
some new kind of monolithic physical structure that implements the
constraint across tables (partitions), right? I'm thinking you mean a
collection of btree indexes on individual partitions with the key of each
index matching the partition key of the parent, created internally as part
of the creation of the same index on the parent. In fact, the said
indexes are created and maintained sort of like how inherited attributes,
constraints are. That would require quite a bit of new infrastructure.
We did discuss about the possibility of such a feature being implemented
on top of declarative partitioning, but not in version 1 [1].

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CA%2BTgmoZZMfcf16YaHuhP1Vk%3Dj8PDFeHCvfj%2BFJQd%2BeFhs%2B7P8A%40mail.gmail.com