Обсуждение: BUG #15662: Row update in foreign partition does not move row based on partition key
BUG #15662: Row update in foreign partition does not move row based on partition key
От
PG Bug reporting form
Дата:
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.
Re: BUG #15662: Row update in foreign partition does not move row based on partition key
От
Sergei Kornilov
Дата:
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
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@zsrv.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."
https://www.postgresql.org/docs/11/sql-update.html
regards, Sergei
Derek
Re: BUG #15662: Row update in foreign partition does not move rowbased on partition key
От
Amit Langote
Дата:
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