Re: Update does not move row across foreign partitions in v11

On Fri, 8 Mar 2019 at 15:07, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> David, can you confirm if the rewritten text reads unambiguous or perhaps
> suggest a better wording?

So this is the text:

+ Currently, rows cannot be moved from a foreign-table partition to some
+ other partition, but they can be moved into a foreign-table partition if
+ the foreign data wrapper supports tuple routing.

I read this to mean that rows cannot normally be moved out of a
foreign-table partition unless the new partition is a foreign one that
uses an FDW that supports tuple routing.

So let's test that:

create extension postgres_fdw ;
do $$ begin execute 'create server loopback foreign data wrapper
postgres_fdw options (dbname ''' || current_database() || ''');'; end;
$$;
create user mapping for current_user server loopback;

create table listp (a int) partition by list (a);
create table listp1 (a int, check (a = 1));
create table listp2 (a int, check (a = 2));

create foreign table listpf1 partition of listp for values in (1)
server loopback options (table_name 'listp1');
create foreign table listpf2 partition of listp for values in (2)
server loopback options (table_name 'listp2');

insert into listp values (1);

update listp set a = 2 where a = 1;
ERROR: new row for relation "listp1" violates check constraint "listp1_a_check"
DETAIL: Failing row contains (2).
CONTEXT: remote SQL command: UPDATE public.listp1 SET a = 2 WHERE ((a = 1))

I'd be filing a bug report for that as I'm moving a row into a foreign
table with an FDW that supports tuple routing.

Where I think you're going wrong is, in one part of the sentence
you're talking about UPDATE, then in the next part you seem to
magically jump to talking about INSERT. Since the entire paragraph is
talking about UPDATE, why is it relevant to talk about INSERT?

I thought my doc_confirm_foreign_partition_limitations.patch had this
pretty clear with:

+ <listitem>
+ <para>
+ Currently, an <command>UPDATE</command> of a partitioned table cannot
+ move rows out of a foreign partition into another partition.
+ </para>
+ </listitem>

Or is my understanding of this incorrect? I also think the new
paragraph is a good move as it's a pretty restrictive limitation for
anyone that wants to set up a partition hierarchy with foreign
partitions.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services