Skip adding row-marks for non target tables when result relation is foreign table.

From: SAIKIRAN AVULA <avulasaikiranreddy(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Skip adding row-marks for non target tables when result relation is foreign table.
Date: 2024-05-06 22:10:33
Message-ID: CABg8Gm5z53jv7+2-=RXZ0XYOna7Ts_Yc+TP83qbBU4YDtbAouA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi PostgreSQL Community,

I would like to bring to your attention an observation regarding the
planner's behavior for foreign table update/delete operations. It appears
that the planner adds rowmarks (ROW_MARK_COPY) for non-target tables, which
I believe is unnecessary when using the postgres-fdw. This is because
postgres-fdw performs early locking on tuples belonging to the target
foreign table by utilizing the SELECT FOR UPDATE clause.

In an attempt to address this, I tried implementing late locking. However,
this approach still doesn't work as intended because the API assumes that
foreign table rows can be re-fetched using TID (ctid). This assumption is
invalid for partitioned tables on the foreign server. Additionally, the
commit afb9249d06f47d7a6d4a89fea0c3625fe43c5a5d, which introduced late
locking for foreign tables, mentions that the benefits of late locking
against a remote server are unclear, as the extra round trips required are
likely to outweigh any potential concurrency improvements.

To address this issue, I have taken the initiative to create a patch that
prevents the addition of rowmarks for non-target tables when the target
table is using early locking. I would greatly appreciate it if you could
review the patch and provide any feedback or insights I may be overlooking.

Example query plan with my change: (foreign scan doesn't fetch whole row
for bar).

postgres=# \d+ bar
Foreign table "public.bar"
Column | Type | Collation | Nullable | Default | FDW options |
Storage | Stats target | Description
--------+---------+-----------+----------+---------+--------------------+---------+--------------+-------------
b1 | integer | | | | (column_name 'b1') |
plain | |
b2 | integer | | | | (column_name 'b2') |
plain | |
Server: postgres_1
FDW options: (schema_name 'public', table_name 'bar')

router=# \d+ foo
Foreign table "public.foo"
Column | Type | Collation | Nullable | Default | FDW options |
Storage | Stats target | Description
--------+---------+-----------+----------+---------+--------------------+---------+--------------+-------------
f1 | integer | | | | (column_name 'f1') |
plain | |
f2 | integer | | | | (column_name 'f2') |
plain | |
Server: postgres_2
FDW options: (schema_name 'public', table_name 'foo')

postgres=# explain verbose update foo set f1 = b1 from bar where f2=b2;
QUERY PLAN

----------------------------------------------------------------------------------------
Update on public.foo (cost=200.00..48713.72 rows=0 width=0)
Remote SQL: UPDATE public.foo SET f1 = $2 WHERE ctid = $1
-> Nested Loop (cost=200.00..48713.72 rows=15885 width=42)
Output: bar.b1, foo.ctid, foo.*
Join Filter: (foo.f2 = bar.b2)
-> Foreign Scan on public.bar (cost=100.00..673.20 rows=2560
width=8)
Output: bar.b1, bar.b2
Remote SQL: SELECT b1, b2 FROM public.bar
-> Materialize (cost=100.00..389.23 rows=1241 width=42)
Output: foo.ctid, foo.*, foo.f2
-> Foreign Scan on public.foo (cost=100.00..383.02
rows=1241 width=42)
Output: foo.ctid, foo.*, foo.f2
Remote SQL: SELECT f1, f2, ctid FROM public.foo FOR
UPDATE
(13 rows)

Thank you for your time and consideration.

Regards
Saikiran Avula
SDE, Amazon Web Services.

Attachment Content-Type Size
v1-0001-Skip-adding-row-marks-for-not-target-relations-wh.patch application/octet-stream 14.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2024-05-06 22:55:28 Re: backend stuck in DataFileExtend
Previous Message Sutou Kouhei 2024-05-06 21:40:11 Re: Is it acceptable making COPY format extendable?