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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15662: Row update in foreign partition does not move row based on partition key
Дата
Msg-id 15662-4b833b46c8a35fe7@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #15662: Row update in foreign partition does not move row based on partition key
Список pgsql-bugs
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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: pgAdmin - Download as CSV
Следующее
От: Sergei Kornilov
Дата:
Сообщение: Re: BUG #15662: Row update in foreign partition does not move row based on partition key