Re: [BUG?] postgres_fdw incorrectly updates remote table if it hasinherited children. - Mailing list pgsql-hackers

Hi,

On Sun, Mar 1, 2020 at 12:00 PM Kohei KaiGai <kaigai@heterodb.com> wrote:
>
> Hello,
>
> I noticed the following scenario under the development of truncate
> support on FDW.
>
> In case when 'ftable' maps a remote table that has inherited children,...
>
> postgres=# create table rtable_parent (id int, label text, x text);
> CREATE TABLE
> postgres=# create table rtable_child () inherits (rtable_parent);
> CREATE TABLE
> postgres=# insert into rtable_parent (select x, 'parent', md5(x::text)
> from generate_series(1,10) x);
> INSERT 0 10
> postgres=# insert into rtable_child (select x, 'child', md5(x::text)
> from generate_series(6,15) x);
> INSERT 0 10
> postgres=# create foreign table ftable (id int, label text, x text)
>                    server loopback options (table_name 'rtable_parent');
> CREATE FOREIGN TABLE
>
> The 'ftable' shows the results from both of the parent and children.
> postgres=# select * from ftable;
>  id | label  |                x
> ----+--------+----------------------------------
>   1 | parent | c4ca4238a0b923820dcc509a6f75849b
>   2 | parent | c81e728d9d4c2f636f067f89cc14862c
>   3 | parent | eccbc87e4b5ce2fe28308fd9f2a7baf3
>   4 | parent | a87ff679a2f3e71d9181a67b7542122c
>   5 | parent | e4da3b7fbbce2345d7772b0674a318d5
>   6 | parent | 1679091c5a880faf6fb5e6087eb1b2dc
>   7 | parent | 8f14e45fceea167a5a36dedd4bea2543
>   8 | parent | c9f0f895fb98ab9159f51fd0297e236d
>   9 | parent | 45c48cce2e2d7fbdea1afc51c7c6ad26
>  10 | parent | d3d9446802a44259755d38e6d163e820
>   6 | child  | 1679091c5a880faf6fb5e6087eb1b2dc
>   7 | child  | 8f14e45fceea167a5a36dedd4bea2543
>   8 | child  | c9f0f895fb98ab9159f51fd0297e236d
>   9 | child  | 45c48cce2e2d7fbdea1afc51c7c6ad26
>  10 | child  | d3d9446802a44259755d38e6d163e820
>  11 | child  | 6512bd43d9caa6e02c990b0a82652dca
>  12 | child  | c20ad4d76fe97759aa27a0c99bff6710
>  13 | child  | c51ce410c124a10e0db5e4b97fc2af39
>  14 | child  | aab3238922bcc25a6f606eb525ffdc56
>  15 | child  | 9bf31c7ff062936a96d3c8bd1f8f2ff3
> (20 rows)
>
> When we try to update the foreign-table without DirectUpdate mode,
> remote query tries to update the rows specified by "ctid" system column.
> However, it was not a unique key in this case.
>
> postgres=# explain update ftable set x = 'updated' where id > 10 and
> pg_backend_pid() > 0;
>                                  QUERY PLAN
> -----------------------------------------------------------------------------
>  Update on ftable  (cost=100.00..133.80 rows=414 width=74)
>    ->  Result  (cost=100.00..133.80 rows=414 width=74)
>          One-Time Filter: (pg_backend_pid() > 0)
>          ->  Foreign Scan on ftable  (cost=100.00..133.80 rows=414 width=42)
> (4 rows)
>
> [*] Note that pg_backend_pid() prevent direct update.
>
> postgres=# update ftable set x = 'updated' where id > 10 and
> pg_backend_pid() > 0;
> UPDATE 5
> postgres=# select ctid,* from ftable;
>   ctid  | id | label  |                x
> --------+----+--------+----------------------------------
>  (0,1)  |  1 | parent | c4ca4238a0b923820dcc509a6f75849b
>  (0,2)  |  2 | parent | c81e728d9d4c2f636f067f89cc14862c
>  (0,3)  |  3 | parent | eccbc87e4b5ce2fe28308fd9f2a7baf3
>  (0,4)  |  4 | parent | a87ff679a2f3e71d9181a67b7542122c
>  (0,5)  |  5 | parent | e4da3b7fbbce2345d7772b0674a318d5
>  (0,11) |  6 | parent | updated
>  (0,12) |  7 | parent | updated
>  (0,13) |  8 | parent | updated
>  (0,14) |  9 | parent | updated
>  (0,15) | 10 | parent | updated
>  (0,1)  |  6 | child  | 1679091c5a880faf6fb5e6087eb1b2dc
>  (0,2)  |  7 | child  | 8f14e45fceea167a5a36dedd4bea2543
>  (0,3)  |  8 | child  | c9f0f895fb98ab9159f51fd0297e236d
>  (0,4)  |  9 | child  | 45c48cce2e2d7fbdea1afc51c7c6ad26
>  (0,5)  | 10 | child  | d3d9446802a44259755d38e6d163e820
>  (0,11) | 11 | child  | updated
>  (0,12) | 12 | child  | updated
>  (0,13) | 13 | child  | updated
>  (0,14) | 14 | child  | updated
>  (0,15) | 15 | child  | updated
> (20 rows)
>
> The WHERE-clause (id > 10) should affect only child table.
> However, it updated the rows in the parent table with same ctid.
>
> How about your thought?
> Probably, we need to fetch a pair of tableoid and ctid to identify
> the remote table exactly, if not direct-update cases.

This was this discussed on this thread:

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

Solutions have been proposed too, but none finalized yet.

Thanks,
Amit