Re: [BUGS] BUG #14759: insert into foreign data partitions fail

On 2017/07/25 9:43, David G. Johnston wrote:
> On Mon, Jul 24, 2017 at 5:19 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
>> wrote:
>
>> On 2017/07/25 6:28, mtuncer(at)gmail(dot)com wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 14759
>>> Logged by: Murat Tuncer
>>> Email address: mtuncer(at)gmail(dot)com
>>> PostgreSQL version: 10beta2
>>> Operating system: Mac 10.12.6
>>> Description:
>>>
>>> I got
>>>
>>> ERROR: cannot route inserted tuples to a foreign table
>>
>> Inserting tuples into a partitioned table that will route to one of its
>> foreign table partitions is unsupported in PG 10. The limitation is
>> mentioned on the following page:
>> https://www.postgresql.org/docs/devel/static/ddl-partitioning.html
>
>
> It would be nice to also note this limitation here:
>
> https://www.postgresql.org/docs/devel/static/sql-createforeigntable.html

Yeah, I thought the same when writing my previous email.

> Also, the ddl-partitioning.html page has a section "5.10.2.3.
> Limitations". Moving (or duplicating maybe) the existing comment on that
> page in that section would make finding out about this limitation a bit
> easier.

Yeah, perhaps.

> I'd probably move (and rework) the "limitation wording" to the limitation
> sections and do something like the following in the main section.
>
> "Foreign Tables can be added to a partitioning structure but inserts to the
> partitioned table will fail if they are routed to a foreign table
> partition. Direct writes to the foreign table, and partition reads, work
> normally."

Done that in the attached.

> I'm curious what the other limitations are...

When I first wrote that documentation line (I am assuming you're asking
about "although these have some limitations that normal tables do not"), I
was thinking about the fact that the core system does not enforce
(locally) any constraints defined on foreign tables. Since we allow
inserting data into partitions directly, it is imperative that we enforce
the "partition constraint" along with the traditional constraints such as
NOT NULL and CHECK constraints, which we can do for local table partitions
but not for foreign table ones.

Anyway, attached patch documents all these limitations about foreign table
partitions more prominently.

Thanks,
Amit