From a3fd53babb8e8bde688739ec367a6d170495cfb4 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 11 Feb 2026 11:03:01 -0500 Subject: [PATCH] Further stabilize a postgres_fdw test case. The buildfarm occasionally shows a variant row order in the output of this UPDATE ... RETURNING, implying that the preceding INSERT dropped one of the rows into some free space within the table rather than appending them all at the end. It's not entirely clear why that happens some times and not other times, but we have established that it's affected by concurrent activity in other databases of the cluster. In any case, the behavior is not wrong; the test is at fault for presuming that a seqscan will give deterministic row ordering. Add an ORDER BY atop the update to stop the buildfarm noise. The buildfarm seems to have shown this only in v18 and master branches, but just in case the cause is older, back-patch to all supported branches. Discussion: https://postgr.es/m/3866274.1770743162@sss.pgh.pa.us Backpatch-through: 14 --- .../postgres_fdw/expected/postgres_fdw.out | 35 ++++++++++++------- contrib/postgres_fdw/sql/postgres_fdw.sql | 11 ++++-- 2 files changed, 32 insertions(+), 14 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7cad5e67d09..2ccb72c539a 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6503,20 +6503,31 @@ UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END ALTER SERVER loopback OPTIONS (DROP extensions); INSERT INTO ft2 (c1,c2,c3) SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id; +-- this will do a remote seqscan, causing unstable result order, so sort EXPLAIN (verbose, costs off) -UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down - QUERY PLAN ----------------------------------------------------------------------------------------------------------- - Update on public.ft2 - Output: c1, c2, c3, c4, c5, c6, c7, c8 - Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 - -> Foreign Scan on public.ft2 - Output: 'bar'::text, ctid, ft2.* - Filter: (postgres_fdw_abs(ft2.c1) > 2000) - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE -(7 rows) +WITH cte AS ( + UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING * +) SELECT * FROM cte ORDER BY c1; -- can't be pushed down + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ + Sort + Output: cte.c1, cte.c2, cte.c3, cte.c4, cte.c5, cte.c6, cte.c7, cte.c8 + Sort Key: cte.c1 + CTE cte + -> Update on public.ft2 + Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8 + Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8 + -> Foreign Scan on public.ft2 + Output: 'bar'::text, ft2.ctid, ft2.* + Filter: (postgres_fdw_abs(ft2.c1) > 2000) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" FOR UPDATE + -> CTE Scan on cte + Output: cte.c1, cte.c2, cte.c3, cte.c4, cte.c5, cte.c6, cte.c7, cte.c8 +(13 rows) -UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; +WITH cte AS ( + UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING * +) SELECT * FROM cte ORDER BY c1; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ------+----+-----+----+----+----+------------+---- 2001 | 1 | bar | | | | ft2 | diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index eff25bd2baa..72d2d9c311b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1613,9 +1613,16 @@ UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END ALTER SERVER loopback OPTIONS (DROP extensions); INSERT INTO ft2 (c1,c2,c3) SELECT id, id % 10, to_char(id, 'FM00000') FROM generate_series(2001, 2010) id; + +-- this will do a remote seqscan, causing unstable result order, so sort EXPLAIN (verbose, costs off) -UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down -UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; +WITH cte AS ( + UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING * +) SELECT * FROM cte ORDER BY c1; -- can't be pushed down +WITH cte AS ( + UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING * +) SELECT * FROM cte ORDER BY c1; + EXPLAIN (verbose, costs off) UPDATE ft2 SET c3 = 'baz' FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1) -- 2.47.3