On 2017/07/25 9:43, David G. Johnston wrote:
> On Mon, Jul 24, 2017 at 5:19 PM, Amit Langote <langote_amit...@lab.ntt.co.jp
>> wrote:
>
>> On 2017/07/25 6:28, mtun...@gmail.com wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 14759
>>> Logged by: Murat Tuncer
>>> Email address: mtun...@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
From 7ba72f024223cbe1a8e1da1220b8f8efb8e8f215 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
