Re: postgres_fdw: Fix flaky push down FUNCTION RTE test

Hi,

On Thu, Aug 20, 2026 at 8:34 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>
> Hi,
>
> The unnest function portion of the test introduced in 0ee83dd4a99
> produced different plans on the two machines I tested with. On my Mac it
> produced the expected plan, but on my Amazon Linux 2 (aarch64) box it
> consistently failed:
>
> The inner and outer sides of the nested loop flipped between the two
> machines, because the costs of the two sides are almost identical. To
> stabilize the test, instead of a range predicate WHERE ((c3 < '00010'))
> we can use an equality WHERE ((c3 = '00010')), which restricts ft1 to a
> single row so it's clearly the outer side of the loop. With the
> equality predicate I get the same plan on both machines.
>
> Attached is a patch to stabilize this test.

Yes, I'm seeing this too on my EC2 instance with Amazon Linux 2
(x86_64) [1]. The attached patch fixes it and looks good to me.

[1]
# --- /local/home/rupiredd/postgres/contrib/postgres_fdw/expected/postgres_fdw.out
2026-08-20 01:24:09.657630184 +0000
# +++ /local/home/rupiredd/postgres/contrib/postgres_fdw/results/postgres_fdw.out
2026-08-21 04:59:48.770837687 +0000
# @@ -3135,20 +3135,20 @@
# SELECT t1.c1, t2.c1
# FROM ft1 t1, ft6 t2, unnest(ARRAY[3, 6, 9, 12, 15, 18]::int[]) AS u(id)
# WHERE t1.c1 = u.id AND t2.c1 = u.id AND t1.c3 < '00010';
# -
QUERY PLAN
# -----------------------------------------------------------------------------------------------------------------------------------------------------
# +
QUERY PLAN
# +----------------------------------------------------------------------------------------------------------------------------------------------
# Nested Loop
# Output: t1.c1, t2.c1
# Join Filter: (t1.c1 = u.id)
# - -> Foreign Scan on public.ft1 t1
# - Output: t1.c1
# - Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE ((c3 < '00010'))
# - -> Materialize
# + -> Foreign Scan
# Output: t2.c1, u.id
# - -> Foreign Scan
# - Output: t2.c1, u.id
# - Relations: (public.ft6 t2) INNER JOIN (pg_catalog.unnest() u)
# - Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2
INNER JOIN unnest('{3,6,9,12,15,18}'::integer[]) f3(c1) ON (((r2.c1 =
f3.c1))))
# + Relations: (public.ft6 t2) INNER JOIN (pg_catalog.unnest() u)
# + Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2 INNER
JOIN unnest('{3,6,9,12,15,18}'::integer[]) f3(c1) ON (((r2.c1 =
f3.c1))))
# + -> Materialize
# + Output: t1.c1
# + -> Foreign Scan on public.ft1 t1
# + Output: t1.c1
# + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE ((c3
< '00010'))
# (12 rows)
#
# -- The remaining scenarios reuse a dedicated foreign table to cover the
not ok 1 - postgres_fdw 6631 ms
ok 2 - query_cancel 30 ms
1..2
# 1 of 2 tests failed.
# The differences that caused some tests to fail can be viewed in the
file "/local/home/rupiredd/postgres/contrib/postgres_fdw/regression.diffs".
# A copy of the test summary that you see above is saved in the file
"/local/home/rupiredd/postgres/contrib/postgres_fdw/regression.out".
make[2]: *** [check] Error 1

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com