Re: BUG #17413: update of partitioned table via postgres_fdw updates to much rows

On Wed, Feb 23, 2022 at 2:09 AM Japin Li <japinli(at)hotmail(dot)com> wrote:
> On Tue, 22 Feb 2022 at 18:03, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> > The following bug has been logged on the website:

> > 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.

Yeah, this is a known issue [1]. I created a patch for the issue, but
the patch needs more work. It’s on my TODO list to revisit the patch.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%40mail.gmail.com