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

On 2017/07/26 15:29, Amit Langote wrote:
> 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...

I think COPY has the same limitation as INSERT.

> 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.

Typo: s/the they is/they are/

Best regards,
Etsuro Fujita