postgres_fdw: Fix flaky push down FUNCTION RTE test

From: Sami Imseih <samimseih(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: akorotkov(at)postgresql(dot)org, a(dot)pyhalov(at)postgrespro(dot)ru
Subject: postgres_fdw: Fix flaky push down FUNCTION RTE test
Date: 2026-08-21 03:34:22
Message-ID: CAA5RZ0uC=GOihOKcF7o3JVETF3e-Fkc4OUO5NbqMEKCTeSN-dQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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:

```
# @@ -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
```

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.

--
Sami Imseih
Amazon Web Services (AWS)

Attachment Content-Type Size
v1-0001-postgres_fdw-Fix-flaky-push-down-FUNCTION-RTE-tes.patch application/octet-stream 4.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-08-21 03:38:57 Re: Fix small psql slash option leaks
Previous Message Zhijie Hou (Fujitsu) 2026-08-21 03:21:41 RE: Parallel Apply