PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15662
PG Version11.2
OSDebian 6.3.0-18+deb9u1
Opened2019-03-01 19:06:48+00
Reported byDerek Hans
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15662
Logged by:          Derek Hans
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2019-03-01 19:06:48+00PG Bug reporting formBUG #15662: Row update in foreign partition does not move row based on partition key
2019-03-01 19:54:00+00Sergei KornilovRe: BUG #15662: Row update in foreign partition does not move row based on partition key
2019-03-04 13:57:48+00Derek HansRe: BUG #15662: Row update in foreign partition does not move row based on partition key
2019-03-04 15:12:25+00Amit LangoteRe: BUG #15662: Row update in foreign partition does not move row based on partition key