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.