Re: BUG #17413: update of partitioned table via postgres_fdw updates to much rows - Mailing list pgsql-bugs
From | Japin Li |
---|---|
Subject | Re: BUG #17413: update of partitioned table via postgres_fdw updates to much rows |
Date | |
Msg-id | MEYP282MB16691CFD2F1D5EC5A80F2201B63B9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM Whole thread Raw |
In response to | BUG #17413: update of partitioned table via postgres_fdw updates to much rows (PG Bug reporting form <noreply@postgresql.org>) |
Responses |
RE: BUG #17413: update of partitioned table via postgres_fdw updates to much rows
(Stepan Yankevych <Stepan_Yankevych@epam.com>)
Re: BUG #17413: update of partitioned table via postgres_fdw updates to much rows (Etsuro Fujita <etsuro.fujita@gmail.com>) |
List | pgsql-bugs |
On Tue, 22 Feb 2022 at 18:03, PG Bug reporting form <noreply@postgresql.org> wrote: > The following bug has been logged on the website: > > Bug reference: 17413 > Logged by: Stepan Yankevych > Email address: stepya@ukr.net > PostgreSQL version: 14.2 > Operating system: CentOS > Description: > > We noticed that update foreign table in some cases passes following update > to the remote DB > update part_update_test > set field=$2 > where ctid=$1 > In that case one row from each partition can be updated. > See steps to reproduce > > -- pgprod1 > drop table if exists trash.part_update_test; > > CREATE TABLE trash.part_update_test ( > id serial, > date_id int4 NOT NULL, > simple_text varchar > ) PARTITION BY RANGE (date_id); > > CREATE TABLE trash.part_update_test_20220221 PARTITION OF > trash.part_update_test FOR VALUES FROM (20220221) TO (20220222); > CREATE TABLE trash.part_update_test_20220222 PARTITION OF > trash.part_update_test FOR VALUES FROM (20220222) TO (20220223); > CREATE TABLE trash.part_update_test_20220223 PARTITION OF > trash.part_update_test FOR VALUES FROM (20220223) TO (20220224); > > insert into trash.part_update_test (date_id, simple_text) > values (20220221, 'Im 20220221'),(20220222, 'I amd 20220222'),(20220223, 'I > am the third row '); > > select ctid, * > from trash.part_update_test; > > ctid |id|date_id |simple_text | > -----+--+--------+-------------------+ > (0,1)| 1|20220221|Im 20220221 | > (0,1)| 2|20220222|I amd 20220222 | > (0,1)| 3|20220223|I am the third row | > > -- pgprod2 > DROP FOREIGN TABLE if EXISTS staging.part_update_test; > IMPORT FOREIGN SCHEMA "trash" LIMIT TO (part_update_test) > FROM SERVER postgresprod > into staging; > > > with ids as materialized (select 1 as id, 20220221 as date_id ) > update staging.part_update_test t > set simple_text = 'I am updated version of 20220221 ' > from ids > where t.id = ids.id > and t.date_id = ids.date_id ; > > select ctid, * > from staging.part_update_test; > > ctid |id|date_id |simple_text | > -----+--+--------+----------------------------------+ > (0,2)| 1|20220221|I am updated version of 20220221 | > (0,2)| 2|20220222|I am updated version of 20220221 | > (0,2)| 3|20220223|I am updated version of 20220221 | > > As you can see all of rows that had (0,1) where updated , but the only first > row with ID =1 had to be updated . > The same was reproducible at least in PG14.1 Yeah, it is a bug IMO. The deparseUpdateSql() function assume that the tuple id is as unique, however, it ignores the partitioned table, in which the tuple id may be duplicated. -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.
pgsql-bugs by date: