agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Jehan-Guillaume de Rorthais <[email protected]>
Subject: [PATCH v3 1/2] Test exposing bug when foreign table points to a partitioned table
Date: Tue, 15 Jul 2025 12:45:21 +0200
When a foreign table points to a partitioned table or an inheritance
parent on the foreign server, a non-direct DML can affect multiple
rows when only one row is intended to be affected. This
happens because postgres_fdw uses only ctid to identify a row to work
on. Though ctid uniquely identifies a row in a single table, in a
partitioned table or in an inheritance hierarchy, there can be be
multiple rows, in different partitions, with the same ctid. So
DML statement sent to the foreign server by postgres_fdw ends up
affecting more than one rows, only one of which is intended to be
affected.
This commit adds testcases to show the problem. A subsequent commit
would have a fix to the problem.
Author: Ashutosh Bapat <[email protected]>
Reviewed-by: Kyotaro Horiguchi <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%4...
Rebased by Jehan-Guillaume de Rorthais <[email protected]>
---
.../postgres_fdw/expected/postgres_fdw.out | 125 ++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 58 ++++++++
2 files changed, 183 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2185b42bb4f..62019eaa881 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8954,6 +8954,131 @@ drop foreign table remt2;
drop table loct1;
drop table loct2;
drop table parent;
+-- test DML statement on a foreign table pointing to an inheritance hierarchy
+-- on the remote server
+CREATE TABLE a(aa TEXT);
+ALTER TABLE a SET (autovacuum_enabled = 'false');
+CREATE TABLE b() INHERITS(a);
+ALTER TABLE b SET (autovacuum_enabled = 'false');
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+CREATE FOREIGN TABLE fa (aa TEXT) SERVER loopback OPTIONS (table_name 'a');
+SELECT tableoid::regclass, ctid, * FROM fa;
+ tableoid | ctid | aa
+----------+-------+-----
+ fa | (0,1) | aaa
+ fa | (0,1) | bbb
+(2 rows)
+
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------
+ Update on public.fa
+ Remote SQL: UPDATE public.a SET aa = $2 WHERE ctid = $1
+ -> Foreign Scan on public.fa
+ Output: CASE WHEN (random() <= '1'::double precision) THEN 'zzzz'::text ELSE NULL::text END, ctid, fa.*
+ Remote SQL: SELECT aa, ctid FROM public.a WHERE ((aa = 'aaa')) FOR UPDATE
+(5 rows)
+
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+SELECT tableoid::regclass, ctid, * FROM fa;
+ tableoid | ctid | aa
+----------+-------+------
+ fa | (0,2) | zzzz
+ fa | (0,1) | bbb
+(2 rows)
+
+-- repopulate tables so that we have rows with same ctid
+TRUNCATE a, b;
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Delete on public.fa
+ Remote SQL: DELETE FROM public.a WHERE ctid = $1
+ -> Foreign Scan on public.fa
+ Output: ctid
+ Filter: (fa.aa = CASE WHEN (random() <= '1'::double precision) THEN 'aaa'::text ELSE 'bbb'::text END)
+ Remote SQL: SELECT aa, ctid FROM public.a FOR UPDATE
+(6 rows)
+
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+SELECT tableoid::regclass, ctid, * FROM fa;
+ tableoid | ctid | aa
+----------+-------+-----
+ fa | (0,1) | bbb
+(1 row)
+
+-- cleanup
+DROP FOREIGN TABLE fa;
+DROP TABLE a CASCADE;
+NOTICE: drop cascades to table b
+-- ===================================================================
+-- test foreign table pointing to a remote partitioned table
+-- ===================================================================
+-- test DML statement on foreign table pointing to a foreign partitioned table
+CREATE TABLE plt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE plt_p1 PARTITION OF plt FOR VALUES IN (1);
+CREATE TABLE plt_p2 PARTITION OF plt FOR VALUES IN (2);
+INSERT INTO plt VALUES (1, 1), (2, 2);
+CREATE FOREIGN TABLE fplt (a int, b int) SERVER loopback OPTIONS (table_name 'plt');
+SELECT tableoid::regclass, ctid, * FROM fplt;
+ tableoid | ctid | a | b
+----------+-------+---+---
+ fplt | (0,1) | 1 | 1
+ fplt | (0,1) | 2 | 2
+(2 rows)
+
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Update on public.fplt
+ Remote SQL: UPDATE public.plt SET b = $2 WHERE ctid = $1
+ -> Foreign Scan on public.fplt
+ Output: CASE WHEN (random() <= '1'::double precision) THEN 10 ELSE 20 END, ctid, fplt.*
+ Remote SQL: SELECT a, b, ctid FROM public.plt WHERE ((a = 1)) FOR UPDATE
+(5 rows)
+
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+SELECT tableoid::regclass, ctid, * FROM fplt;
+ tableoid | ctid | a | b
+----------+-------+---+----
+ fplt | (0,2) | 1 | 10
+ fplt | (0,1) | 2 | 2
+(2 rows)
+
+-- repopulate partitioned table so that we have rows with same ctid
+TRUNCATE plt;
+INSERT INTO plt VALUES (1, 1), (2, 2);
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Delete on public.fplt
+ Remote SQL: DELETE FROM public.plt WHERE ctid = $1
+ -> Foreign Scan on public.fplt
+ Output: ctid
+ Filter: (fplt.a = CASE WHEN (random() <= '1'::double precision) THEN 1 ELSE 10 END)
+ Remote SQL: SELECT a, ctid FROM public.plt FOR UPDATE
+(6 rows)
+
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+SELECT tableoid::regclass, ctid, * FROM fplt;
+ tableoid | ctid | a | b
+----------+-------+---+---
+ fplt | (0,1) | 2 | 2
+(1 row)
+
+DROP TABLE plt;
+DROP FOREIGN TABLE fplt;
-- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e534b40de3c..ae3777b7edb 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2494,6 +2494,64 @@ drop table loct1;
drop table loct2;
drop table parent;
+-- test DML statement on a foreign table pointing to an inheritance hierarchy
+-- on the remote server
+CREATE TABLE a(aa TEXT);
+ALTER TABLE a SET (autovacuum_enabled = 'false');
+CREATE TABLE b() INHERITS(a);
+ALTER TABLE b SET (autovacuum_enabled = 'false');
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+CREATE FOREIGN TABLE fa (aa TEXT) SERVER loopback OPTIONS (table_name 'a');
+
+SELECT tableoid::regclass, ctid, * FROM fa;
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+SELECT tableoid::regclass, ctid, * FROM fa;
+-- repopulate tables so that we have rows with same ctid
+TRUNCATE a, b;
+INSERT INTO a(aa) VALUES('aaa');
+INSERT INTO b(aa) VALUES('bbb');
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+SELECT tableoid::regclass, ctid, * FROM fa;
+
+-- cleanup
+DROP FOREIGN TABLE fa;
+DROP TABLE a CASCADE;
+
+-- ===================================================================
+-- test foreign table pointing to a remote partitioned table
+-- ===================================================================
+
+-- test DML statement on foreign table pointing to a foreign partitioned table
+CREATE TABLE plt (a int, b int) PARTITION BY LIST(a);
+CREATE TABLE plt_p1 PARTITION OF plt FOR VALUES IN (1);
+CREATE TABLE plt_p2 PARTITION OF plt FOR VALUES IN (2);
+INSERT INTO plt VALUES (1, 1), (2, 2);
+CREATE FOREIGN TABLE fplt (a int, b int) SERVER loopback OPTIONS (table_name 'plt');
+SELECT tableoid::regclass, ctid, * FROM fplt;
+-- use random() so that DML statement is not pushed down to the foreign
+-- server
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+SELECT tableoid::regclass, ctid, * FROM fplt;
+-- repopulate partitioned table so that we have rows with same ctid
+TRUNCATE plt;
+INSERT INTO plt VALUES (1, 1), (2, 2);
+EXPLAIN (VERBOSE, COSTS OFF)
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+SELECT tableoid::regclass, ctid, * FROM fplt;
+
+DROP TABLE plt;
+DROP FOREIGN TABLE fplt;
+
-- ===================================================================
-- test tuple routing for foreign-table partitions
-- ===================================================================
--
2.50.0
--MP_/4ZRYdF7Ah.pt5w65PuZdaPz
Content-Type: text/x-patch
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename=v3-0002-Error-out-if-one-iteration-of-non-direct-DML-affe.patch
view thread (816+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v3 1/2] Test exposing bug when foreign table points to a partitioned table
In-Reply-To: <no-message-id-495270@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox