BUG #14759: insert into foreign data partitions fail
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
when trying to insert into a partitioned table when the target is a foreign
table.
First observed the error when trying to have cstore_fdw tables as partitions
of a regular table. Reproduced the problem with postgres_fdw too.
Details
- create table ptest(a int, b int) partition by range (a);
- create table pt1 partition of ptest for values from (1) to (10);
- create foreign table pt2 partition of ptest for values from (11) to (20)
server foreign_server;
- insert into ptest values (1, 2) ---> works
- insert into ptest values (11, 12) --> fails with mentioned error
message.
I had successfully reproduced this for cstore_fdw and postgres_fdw servers.
Insert works fine if I try to insert into partition table directly.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
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
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
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.
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."
I'm curious what the other limitations are...
David J.
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.htmlIt 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
Attachments:
0001-Clarify-that-partition-constraint-is-not-enforced-on.patchtext/plain; charset=UTF-8; name=0001-Clarify-that-partition-constraint-is-not-enforced-on.patchDownload
From 7ba72f024223cbe1a8e1da1220b8f8efb8e8f215 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 3 Apr 2017 16:45:15 +0900
Subject: [PATCH] Clarify that partition constraint is not enforced on foreign
tables
---
doc/src/sgml/ddl.sgml | 15 ++++++++++++---
doc/src/sgml/ref/create_foreign_table.sgml | 17 +++++++++++------
2 files changed, 23 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b05a9c2150..e7a10e15d3 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2988,9 +2988,10 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
Partitions can also be foreign tables
(see <xref linkend="sql-createforeigntable">),
- although these have some limitations that normal tables do not. For
- example, data inserted into the partitioned table is not routed to
- foreign table partitions.
+ although they have some limitations that normal tables do not. For
+ example, routing the data inserted into the partitioned table to foreign
+ table partitions is not supported, nor are the partition constraints
+ enforced when the data is directly inserted into them.
</para>
<sect3 id="ddl-partitioning-declarative-example">
@@ -3297,6 +3298,14 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
not the partitioned table.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ Routing tuples to partitions that are foreign tables is not supported.
+ So, if an inserted tuple routes to one of the foreign partitions, an
+ error will occur.
+ </para>
+ </listitem>
</itemizedlist>
</para>
</sect3>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 065c982082..12087ec05c 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -79,7 +79,9 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<para>
If <literal>PARTITION OF</literal> clause is specified then the table is
created as a partition of <literal>parent_table</literal> with specified
- bounds.
+ bounds. Note that routing tuples to partitions that are foreign tables
+ is not supported. So, if an inserted tuple routes to one of foreign
+ partitions, an error will occur.
</para>
<para>
@@ -279,16 +281,19 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<title>Notes</title>
<para>
- Constraints on foreign tables (such as <literal>CHECK</>
- or <literal>NOT NULL</> clauses) are not enforced by the
- core <productname>PostgreSQL</> system, and most foreign data wrappers
- do not attempt to enforce them either; that is, the constraint is
+ Constraints (both the user-defined constraints such as <literal>CHECK</>
+ or <literal>NOT NULL</> clauses and the partition constraint) are not
+ enforced by the core <productname>PostgreSQL</> system, and most foreign
+ data wrappers do not attempt to enforce them either; that is, the they is
simply assumed to hold true. There would be little point in such
enforcement since it would only apply to rows inserted or updated via
the foreign table, and not to rows modified by other means, such as
directly on the remote server. Instead, a constraint attached to a
foreign table should represent a constraint that is being enforced by
- 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.
</para>
<para>
--
2.11.0
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
Attachments:
0001-Clarify-that-partition-constraint-is-not-enforced-on-v2.patchtext/plain; charset=UTF-8; name=0001-Clarify-that-partition-constraint-is-not-enforced-on-v2.patchDownload
From 2b9c28808b725ed4551a2876a187531439f13928 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 3 Apr 2017 16:45:15 +0900
Subject: [PATCH] Clarify that partition constraint is not enforced on foreign
tables
---
doc/src/sgml/ddl.sgml | 16 +++++++++++++---
doc/src/sgml/ref/create_foreign_table.sgml | 17 +++++++++++------
2 files changed, 24 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b05a9c2150..a0ab648928 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2988,9 +2988,11 @@ VALUES ('Albany', NULL, NULL, 'NY');
<para>
Partitions can also be foreign tables
(see <xref linkend="sql-createforeigntable">),
- although these have some limitations that normal tables do not. For
- example, data inserted into the partitioned table is not routed to
- foreign table partitions.
+ although they have some limitations that normal tables do not. For
+ example, routing the data inserted (or copied) into the partitioned table
+ to foreign table partitions is not supported, nor are the partition
+ constraints enforced when the data is directly inserted (or copied) into
+ them.
</para>
<sect3 id="ddl-partitioning-declarative-example">
@@ -3297,6 +3299,14 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
not the partitioned table.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ Routing tuples to partitions that are foreign tables is not supported.
+ So, if a tuple inserted (or copied) into the table routes to one of
+ the foreign partitions, an error will occur.
+ </para>
+ </listitem>
</itemizedlist>
</para>
</sect3>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 065c982082..43a6cbcfab 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -79,7 +79,9 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<para>
If <literal>PARTITION OF</literal> clause is specified then the table is
created as a partition of <literal>parent_table</literal> with specified
- bounds.
+ bounds. Note that routing tuples to partitions that are foreign tables
+ is not supported. So, if a tuple inserted (or copied) into the table
+ routes to one of the foreign partitions, an error will occur.
</para>
<para>
@@ -279,16 +281,19 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<title>Notes</title>
<para>
- Constraints on foreign tables (such as <literal>CHECK</>
- or <literal>NOT NULL</> clauses) are not enforced by the
- core <productname>PostgreSQL</> system, and most foreign data wrappers
- do not attempt to enforce them either; that is, the constraint is
+ Constraints (both the user-defined constraints such as <literal>CHECK</>
+ or <literal>NOT NULL</> clauses and the partition constraint) are not
+ enforced by the core <productname>PostgreSQL</> system, and most foreign
+ data wrappers do not attempt to enforce them either; that is, they are
simply assumed to hold true. There would be little point in such
enforcement since it would only apply to rows inserted or updated via
the foreign table, and not to rows modified by other means, such as
directly on the remote server. Instead, a constraint attached to a
foreign table should represent a constraint that is being enforced by
- 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.
</para>
<para>
--
2.11.0
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).
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
Attachments:
0001-Clarify-that-partition-constraint-is-not-enforced-on-v3.patchtext/plain; charset=UTF-8; name=0001-Clarify-that-partition-constraint-is-not-enforced-on-v3.patchDownload
From f79f98710a5bf6bd1b0a921ed2e57fa510c6ac60 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 3 Apr 2017 16:45:15 +0900
Subject: [PATCH] Clarify that partition constraint is not enforced on foreign
tables
---
doc/src/sgml/ddl.sgml | 8 +++-----
doc/src/sgml/ref/create_foreign_table.sgml | 17 +++++++++++------
2 files changed, 14 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b05a9c2150..a707c3e22a 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2986,11 +2986,9 @@ VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
- Partitions can also be foreign tables
- (see <xref linkend="sql-createforeigntable">),
- although these have some limitations that normal tables do not. For
- example, data inserted into the partitioned table is not routed to
- foreign table partitions.
+ Partitions can also be foreign tables, although they have some limitations
+ that normal tables do not; see <xref linkend="sql-createforeigntable"> for
+ more information.
</para>
<sect3 id="ddl-partitioning-declarative-example">
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 065c982082..43a6cbcfab 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -79,7 +79,9 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<para>
If <literal>PARTITION OF</literal> clause is specified then the table is
created as a partition of <literal>parent_table</literal> with specified
- bounds.
+ bounds. Note that routing tuples to partitions that are foreign tables
+ is not supported. So, if a tuple inserted (or copied) into the table
+ routes to one of the foreign partitions, an error will occur.
</para>
<para>
@@ -279,16 +281,19 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<title>Notes</title>
<para>
- Constraints on foreign tables (such as <literal>CHECK</>
- or <literal>NOT NULL</> clauses) are not enforced by the
- core <productname>PostgreSQL</> system, and most foreign data wrappers
- do not attempt to enforce them either; that is, the constraint is
+ Constraints (both the user-defined constraints such as <literal>CHECK</>
+ or <literal>NOT NULL</> clauses and the partition constraint) are not
+ enforced by the core <productname>PostgreSQL</> system, and most foreign
+ data wrappers do not attempt to enforce them either; that is, they are
simply assumed to hold true. There would be little point in such
enforcement since it would only apply to rows inserted or updated via
the foreign table, and not to rows modified by other means, such as
directly on the remote server. Instead, a constraint attached to a
foreign table should represent a constraint that is being enforced by
- 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.
</para>
<para>
--
2.11.0
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)"
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
Attachments:
0001-Clarify-that-partition-constraint-is-not-enforced-on-v4.patchtext/plain; charset=UTF-8; name=0001-Clarify-that-partition-constraint-is-not-enforced-on-v4.patchDownload
From a5b75278a6dab39c5cd7a95a746c28ed8d5f1bbc Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 3 Apr 2017 16:45:15 +0900
Subject: [PATCH] Clarify that partition constraint is not enforced on foreign
tables
---
doc/src/sgml/ddl.sgml | 8 +++-----
doc/src/sgml/ref/create_foreign_table.sgml | 17 +++++++++++------
2 files changed, 14 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b05a9c2150..a707c3e22a 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2986,11 +2986,9 @@ VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
- Partitions can also be foreign tables
- (see <xref linkend="sql-createforeigntable">),
- although these have some limitations that normal tables do not. For
- example, data inserted into the partitioned table is not routed to
- foreign table partitions.
+ Partitions can also be foreign tables, although they have some limitations
+ that normal tables do not; see <xref linkend="sql-createforeigntable"> for
+ more information.
</para>
<sect3 id="ddl-partitioning-declarative-example">
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 065c982082..594f75e112 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -79,7 +79,9 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<para>
If <literal>PARTITION OF</literal> clause is specified then the table is
created as a partition of <literal>parent_table</literal> with specified
- bounds.
+ bounds. Note that routing tuples to partitions that are foreign tables
+ is not supported. So, if a tuple inserted (or copied) into the table
+ routes to one of the foreign partitions, an error will occur.
</para>
<para>
@@ -279,16 +281,19 @@ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
<title>Notes</title>
<para>
- Constraints on foreign tables (such as <literal>CHECK</>
- or <literal>NOT NULL</> clauses) are not enforced by the
- core <productname>PostgreSQL</> system, and most foreign data wrappers
- do not attempt to enforce them either; that is, the constraint is
+ Constraints (both user-defined constraints, such as <literal>CHECK</>
+ or <literal>NOT NULL</> clauses, and the partition constraint) are not
+ enforced by the core <productname>PostgreSQL</> system, and most foreign
+ data wrappers do not attempt to enforce them either; that is, they are
simply assumed to hold true. There would be little point in such
enforcement since it would only apply to rows inserted or updated via
the foreign table, and not to rows modified by other means, such as
directly on the remote server. Instead, a constraint attached to a
foreign table should represent a constraint that is being enforced by
- 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.
</para>
<para>
--
2.11.0
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers