BUG #15662: Row update in foreign partition does not move row based on partition key

The following bug has been logged on the website:

Bug reference: 15662
Logged by: Derek Hans
Email address: derek(dot)hans(at)gmail(dot)com
PostgreSQL version: 11.2
Operating system: Debian 6.3.0-18+deb9u1
Description:

In a table with data partitions, updating the partition key of a row only
moves the row across partitions in some of the situations:
- From local partition to local partition
- From local partition to foreign partition
Rows are not moved
- From foreign partition to local partition
- From foreign partition to foreign partition

Repro steps:
Assumes 2 servers. 1st server has user postgres with password 'docker'
available at localhost:5432

-------------BOX 1
CREATE TABLE temperatures_2016 (
at date,
city text,
mintemp integer,
maxtemp integer
);

-------------BOX 2
CREATE TABLE temperatures (
at date,
city text,
mintemp integer,
maxtemp integer
)
PARTITION BY RANGE (at);

CREATE TABLE temperatures_2017
PARTITION OF temperatures
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');

CREATE EXTENSION postgres_fdw;

CREATE SERVER box1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'postgres', port '5432');

CREATE USER MAPPING FOR postgres SERVER box1
OPTIONS (user 'postgres', password 'docker');

CREATE FOREIGN TABLE temperatures_2016
PARTITION OF temperatures
FOR VALUES FROM ('2016-01-01') TO ('2017-01-01')
SERVER box1;

INSERT INTO temperatures (at, city, mintemp, maxtemp) VALUES ('2016-08-03',
'London', 63, 73);
UPDATE temperatures set at = '2017-01-02';
SELECT * from temperatures_2017;

-----------------------
EXPECTED OUTPUT:
"2017-01-02" "London" 63 73

ACTUAL OUTPUT:
no data.

Running
SELECT * from temperatures_2016;
does output the expected row - i.e. the row remains in the partition where
it was created.

Hello

This is known limitation and documented in the notes section of the UPDATE reference page:

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

/docs/11/sql-update.html

regards, Sergei

Thanks. That's a substantial limitation.

I looked pretty hard through the various pieces of documentation and
tutorials and saw no mention. The v11 release notes don't mention it
either. Might be worth adding to the docs in more places and more
prominently.

On Fri, Mar 1, 2019 at 2:54 PM Sergei Kornilov <sk(at)zsrv(dot)org> wrote:

> Hello
>
> This is known limitation and documented in the notes section of the UPDATE
> reference page:
>
> > "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."
>
> /docs/11/sql-update.html
>
> regards, Sergei
>

--
*Derek*
+1 (415) 754-0519 | derek(dot)hans(at)gmail(dot)com | Skype: derek.hans

Hi,

On Mon, Mar 4, 2019 at 10:58 PM Derek Hans <derek(dot)hans(at)gmail(dot)com> wrote:
>
> Thanks. That's a substantial limitation.
>
> I looked pretty hard through the various pieces of documentation and tutorials and saw no mention. The v11 release notes don't mention it either. Might be worth adding to the docs in more places and more prominently.

Perhaps, it would make sense to make a note related to this in the
partitioning chapter where various limitations are listed and link to
it from the v11 release notes.

I've attached a patch. It applies to the PG 11 stable branch, but the
ddl.sgml part of the patch should also be applied to master.

Thanks,
Amit