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

(2019/03/08 19:29), David Rowley wrote:
> 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.

Fair enough.

> 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?

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?

> 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.

+1

Best regards,
Etsuro Fujita