Thread: Re: [HACKERS] [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@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... 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
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
Thank you for weighing in and reviewing the patch. On 2017/07/28 20:55, Etsuro Fujita wrote: > 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 wrote: >>> I'm curious what the other limitations are... > > I think COPY has the same limitation as INSERT. Yes. I updated the patch to mention that as well. >> 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/ Fixed in the attached. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] [BUGS] BUG #14759: insert into foreign data partitions fail
From
"David G. Johnston"
Date:
> 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.
The revised patch down-thread looks good. Thanks.
I indeed was referring to the paragraph you quoted.
I would probably just s/For example/In particular/ and call it good - or maybe also tell the user that all the limitations are listed in the notes section for create foreign table (though my first thoughts are all quite wordy).
David J.
On 2017/08/01 6:41, David G. Johnston wrote: > On Tue, Jul 25, 2017 at 11:29 PM, Amit Langote < > Langote_Amit_f8@lab.ntt.co.jp> wrote: > >>> 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. >> > > The revised patch down-thread looks good. Thanks. > > I indeed was referring to the paragraph you quoted. > > I would probably just s/For example/In particular/ and call it good - > or maybe also tell the user that all the limitations are listed in the > notes section for create foreign table (though my first thoughts are all > quite wordy). Thanks for the review. On a second thought though, I think we should list the foreign table partitions' limitations in only one place, that is, the CREATE FOREIGN TABLE reference page. Listing them under 5.10.2.3. seems a bit off to me, because other limitations listed there are those of the new partitioned table objects, such as lack of global index constraints, etc. Lack of tuple-routing to foreign partitions does not seem to me of the similar nature. Also, the same text is no longer repeated in 3 different places. Thoughts on the updated patch? Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] [BUGS] BUG #14759: insert into foreign data partitions fail
From
"David G. Johnston"
Date:
On a second thought though, I think we should list the foreign table
partitions' limitations in only one place, that is, the CREATE FOREIGN
TABLE reference page. Listing them under 5.10.2.3. seems a bit off to me,
because other limitations listed there are those of the new partitioned
table objects, such as lack of global index constraints, etc. Lack of
tuple-routing to foreign partitions does not seem to me of the similar
nature. Also, the same text is no longer repeated in 3 different places.
Thoughts on the updated patch?
Overall, works for me.
grammar (add a couple of commas for flow) and style (dropping the first "the")
current: "(both the user-defined constraints such as <literal>CHECK</> or <literal>NOT NULL</> clauses and the partition constraint)"
proposed: "(both user-defined constraints, such as <literal>CHECK</> or <literal>NOT NULL</> clauses, and the partition constraint)"
Thanks!
David J.
On 2017/08/01 10:10, David G. Johnston wrote: > On Mon, Jul 31, 2017 at 5:42 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp >> wrote: > >> >> On a second thought though, I think we should list the foreign table >> partitions' limitations in only one place, that is, the CREATE FOREIGN >> TABLE reference page. Listing them under 5.10.2.3. seems a bit off to me, >> because other limitations listed there are those of the new partitioned >> table objects, such as lack of global index constraints, etc. Lack of >> tuple-routing to foreign partitions does not seem to me of the similar >> nature. Also, the same text is no longer repeated in 3 different places. >> >> Thoughts on the updated patch? >> > > Overall, works for me. > > grammar (add a couple of commas for flow) and style (dropping the first > "the") > > current: "(both the user-defined constraints such as <literal>CHECK</> or > <literal>NOT NULL</> clauses and the partition constraint)" > proposed: "(both user-defined constraints, such as <literal>CHECK</> or > <literal>NOT NULL</> clauses, and the partition constraint)" Good points; fixed in the updated patch. Thanks, Amit -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Attachment
On 2017/08/01 10:18, Amit Langote wrote: > Good points; fixed in the updated patch. I should have mentioned this in an earlier mail, but one thing I noticed is this: - the remote server. + the remote server. That becomes especially important if the table is + being used in a partition hierarchy, where it is recommended to add + a constraint matching the partition constraint expression on + the remote table. I think this would apply to CHECK constraints on foreign tables when implementing partitioning with inheritance. Why do we only mention this for partition constraints? Other than that, the patch looks good to me. Best regards, Etsuro Fujita
On 2017/08/01 12:45, Etsuro Fujita wrote: > On 2017/08/01 10:18, Amit Langote wrote: >> Good points; fixed in the updated patch. > > I should have mentioned this in an earlier mail, but one thing I noticed > is this: > > - the remote server. > + the remote server. That becomes especially important if the table is > + being used in a partition hierarchy, where it is recommended to add > + a constraint matching the partition constraint expression on > + the remote table. > > I think this would apply to CHECK constraints on foreign tables when > implementing partitioning with inheritance. Why do we only mention this > for partition constraints? One thing to consider might be that while a user can mark user-defined CHECK constraints as being NOT VALID so that the planner doesn't consider them during constraint exclusion, the same cannot be done for internally generated partition constraints. Maybe, (for time being?), the planner should be taught to disregard foreign tables' partition constraint (if any) for constraint exclusion. > Other than that, the patch looks good to me. Thanks for the review. Thanks, Amit