Hi all,
We have been bitten by this old bug recently:
https://www.postgresql.org/message-id/flat/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%40mail.gmail.com
I suspect this bug lost attention when the only fixed submitted to the
commitfest has been flagged as "returned with feedback":
https://commitfest.postgresql.org/patch/1819/
Please, find in attachment the old patch forbidding more than one row to be
deleted/updated from postgresExecForeignDelete and postgresExecForeignUpdate. I
just rebased them without modification. I suppose this is much safer than
leaving the FDW destroy arbitrary rows on the remote side based on their sole
ctid.
The original discussion talked about using "WHERE CURRENT OF" with cursors to
update/delete rows but discard it because of performance penalty. As adding
tableoid as a junk clause as been rejected, should we investigate the former?
At least for existing major release?
Or maybe we should just not support foreign table to reference a
remote partitioned table?
I'm afraid other fix suggestions from 2018-2019 couldn't be backported as they
seem to require additional feature in FDW altogether. This might be another
reason this bug has been forgotten.
Regards,
>From d93f026e74659d3387a0ca1bbd8ae94fb0c240e1 Mon Sep 17 00:00:00 2001
From: Jehan-Guillaume de Rorthais <j...@dalibo.com>
Date: Tue, 15 Jul 2025 12:45:21 +0200
Subject: [PATCH v3 1/2] Test exposing bug when foreign table points to a
partitioned table
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 <ashutosh.bapat....@gmail.com>
Reviewed-by: Kyotaro Horiguchi <horikyota....@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAFjFpRfcgwsHRmpvoOK-GUQi-n8MgAS%2BOxcQo%3DaBDn1COywmcg%40mail.gmail.com
Rebased by Jehan-Guillaume de Rorthais <j...@dalibo.com>
---
.../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
>From 20d78db73ecc0ccea34d6cab03e9d882564493f6 Mon Sep 17 00:00:00 2001
From: Jehan-Guillaume de Rorthais <j...@dalibo.com>
Date: Fri, 18 Jul 2025 16:52:38 +0200
Subject: [PATCH v3 2/2] Error out if one iteration of non-direct DML affects
more than one row on the foreign server
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 a 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.
In such a case it's good to throw an error instead of corrupting
remote database with unwanted UPDATE/DELETEs. Subsequent commits will
try to fix this situation.
Author: Ashutosh Bapat <ashutosh.bapat....@gmail.com>
Author: Kyotaro Horiguchi <horikyota....@gmail.com>
Rebased by Jehan-Guillaume de Rorthais <j...@dalibo.com>
---
.../postgres_fdw/expected/postgres_fdw.out | 26 ++++++++------
contrib/postgres_fdw/postgres_fdw.c | 36 +++++++++++++++----
2 files changed, 46 insertions(+), 16 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 62019eaa881..b0ef54a2889 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8984,10 +8984,11 @@ UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa
(5 rows)
UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa';
+ERROR: foreign server affected 2 rows when only one was expected
SELECT tableoid::regclass, ctid, * FROM fa;
- tableoid | ctid | aa
-----------+-------+------
- fa | (0,2) | zzzz
+ tableoid | ctid | aa
+----------+-------+-----
+ fa | (0,1) | aaa
fa | (0,1) | bbb
(2 rows)
@@ -9008,11 +9009,13 @@ DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
(6 rows)
DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END);
+ERROR: foreign server affected 2 rows when only one was expected
SELECT tableoid::regclass, ctid, * FROM fa;
- tableoid | ctid | aa
+ tableoid | ctid | aa
----------+-------+-----
+ fa | (0,1) | aaa
fa | (0,1) | bbb
-(1 row)
+(2 rows)
-- cleanup
DROP FOREIGN TABLE fa;
@@ -9048,10 +9051,11 @@ UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
(5 rows)
UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
+ERROR: foreign server affected 2 rows when only one was expected
SELECT tableoid::regclass, ctid, * FROM fplt;
- tableoid | ctid | a | b
-----------+-------+---+----
- fplt | (0,2) | 1 | 10
+ tableoid | ctid | a | b
+----------+-------+---+---
+ fplt | (0,1) | 1 | 1
fplt | (0,1) | 2 | 2
(2 rows)
@@ -9071,11 +9075,13 @@ DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
(6 rows)
DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END);
+ERROR: foreign server affected 2 rows when only one was expected
SELECT tableoid::regclass, ctid, * FROM fplt;
tableoid | ctid | a | b
----------+-------+---+---
- fplt | (0,1) | 2 | 2
-(1 row)
+ fplt | (0,1) | 1 | 1
+ fplt | (0,1) | 2 | 2
+(2 rows)
DROP TABLE plt;
DROP FOREIGN TABLE fplt;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index e0a34b27c7c..09c87d0e5d8 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -4132,7 +4132,8 @@ execute_foreign_modify(EState *estate,
ItemPointer ctid = NULL;
const char **p_values;
PGresult *res;
- int n_rows;
+ int n_rows_returned;
+ int n_rows_affected;
StringInfoData sql;
/* The operation should be INSERT, UPDATE, or DELETE */
@@ -4213,27 +4214,50 @@ execute_foreign_modify(EState *estate,
pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query);
/* Check number of rows affected, and fetch RETURNING tuple if any */
+ n_rows_affected = atoi(PQcmdTuples(res));
if (fmstate->has_returning)
{
Assert(*numSlots == 1);
- n_rows = PQntuples(res);
- if (n_rows > 0)
+ n_rows_returned = PQntuples(res);
+ if (n_rows_returned > 0)
store_returning_result(fmstate, slots[0], res);
+
+ // FIXME: shouldn't we check the max number of rows returned is one?
}
else
- n_rows = atoi(PQcmdTuples(res));
+ n_rows_returned = 0;
/* And clean up */
PQclear(res);
MemoryContextReset(fmstate->temp_cxt);
- *numSlots = n_rows;
+ /*
+ * UPDATE & DELETE command can only affect one row, make sure this contract
+ * is respected.
+ * CMD_INSERT can insert multiple row when called from ForeignBatchInsert.
+ */
+ if (operation != CMD_INSERT)
+ {
+ /* No rows should be returned if no rows were affected */
+ if (n_rows_affected == 0 && n_rows_returned != 0)
+ elog(ERROR, "foreign server returned %d rows when no row was affected",
+ n_rows_returned);
+
+ /* ERROR if more than one row was updated on the remote end */
+ if (n_rows_affected > 1)
+ ereport(ERROR,
+ (errcode (ERRCODE_FDW_ERROR), /* XXX */
+ errmsg ("foreign server affected %d rows when only one was expected",
+ n_rows_affected)));
+ }
+
+ *numSlots = n_rows_returned;
/*
* Return NULL if nothing was inserted/updated/deleted on the remote end
*/
- return (n_rows > 0) ? slots : NULL;
+ return (n_rows_affected > 0) ? slots : NULL;
}
/*
--
2.50.0
