Re: [HACKERS] [BUGS] BUG #14759: insert into foreign data partitionsfail

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@lab.ntt.co.jp
>>> wrote:
>>> On 2017/07/25 6:28, mtuncer@gmail.com wrote:
>>>> The following bug has been logged on the website:
>>>>
>>>> Bug reference:      14759
>>>> Logged by:          Murat Tuncer
>>>> Email address:      mtuncer@gmail.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