Re: Update does not move row across foreign partitions in v11 - Mailing list pgsql-hackers

On Fri, Mar 8, 2019 at 8:21 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:
>
> On Sat, 9 Mar 2019 at 00:09, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
> > IMO I think it's better that we also mention that the UPDATE can move
> > rows into a foreign partition if the FDW supports it.  No?
>
> In my opinion, there's not much need to talk about what the
> limitations are not when you're mentioning what the limitations are.

In this case, I think it might be OK to contrast the two cases so that
it's clear exactly which side doesn't work and which works.  We also
have the following text in limitations:

     <listitem>
      <para>
       While primary keys are supported on partitioned tables, foreign
       keys referencing partitioned tables are not supported.  (Foreign key
       references from a partitioned table to some other table are supported.)
      </para>
     </listitem>

> Maybe it would be worth it if the text was slightly unclear on what's
> affected, but I thought my version was fairly clear.
>
> If you think that it's still unclear, then I wouldn't object to adding
> "  There is no such restriction on <command>UPDATE</command> row
> movements out of native partitions into foreign ones.".  Obviously,
> it's got to be clear for everyone, not just the person who wrote it.

OK, how about this:

--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3877,6 +3877,15 @@ ALTER TABLE measurement ATTACH PARTITION
measurement_y2008m02
       </para>
      </listitem>

+     <listitem>
+      <para>
+       While rows can be moved from local partitions to a foreign-table
+       partition (provided the foreign data wrapper supports tuple routing),
+       they cannot be moved from a foreign-table partition to some
+       other partition.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        <literal>BEFORE ROW</literal> triggers, if necessary, must be defined

diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 77430a586c..f5cf8eab85 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -291,9 +291,9 @@ UPDATE <replaceable class="parameter">count</replaceable>
    concurrent <command>UPDATE</command> or <command>DELETE</command> on the
    same row may miss this row. For details see the section
    <xref linkend="ddl-partitioning-declarative-limitations"/>.
-   Currently, rows cannot be moved from a partition that is a
-   foreign table to some other partition, but they can be moved into a foreign
-   table if the foreign data wrapper supports it.
+   While rows can be moved from local partitions to a foreign-table partition
+   partition (provided the foreign data wrapper supports tuple routing), they
+   cannot be moved from a foreign-table partition to some other partition.
   </para>
  </refsect1>

At least in update.sgml, we describe that row movement is implemented
as DELETE+INSERT, so I think maybe it's OK to mention tuple routing
when mentioning why that 1-way movement works.  If someone's using an
FDW that doesn't support tuple routing to begin with, they'll be get
an error when trying to move rows from a local partition to a foreign
table partition using that FDW, which is this:

ERROR:  cannot route inserted tuples to a foreign table

Then maybe they will come back to the read limitations and see why the
tuple movement didn't work.

Thanks,
Amit