Thread: 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.hans@gmail.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
movedinto a foreign table if the foreign data wrapper supports it."
 

https://www.postgresql.org/docs/11/sql-update.html

regards, Sergei

Hi,

On Mon, Mar 4, 2019 at 10:58 PM Derek Hans <derek.hans@gmail.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
notesdon'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