postgres_fdw: Fix flaky push down FUNCTION RTE test
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253505psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 24, 2026 at 03:50 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253505_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253505_1 && git checkout t253505_1Patchset v1 (message #1) is on t253505_1
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)
Hi,
On Thu, Aug 20, 2026 at 8:34 PM Sami Imseih <samimseih@gmail.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]# --- /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. 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
# --- /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
On Fri, Aug 21, 2026 at 4:44 PM Sami Imseih <samimseih@gmail.com> wrote:
Thanks for reviewing!
Sorry for reacting slow on this. Generally looks good to me. I'm
going to do some sanity check on this and push.
------
Regards,
Alexander Korotkov
Supabase
On Mon, Aug 24, 2026 at 6:27 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
On Fri, Aug 21, 2026 at 4:44 PM Sami Imseih <samimseih@gmail.com> wrote:
Thanks for reviewing!
Sorry for reacting slow on this. Generally looks good to me. I'm
going to do some sanity check on this and push.
Pushed. I only fixed typo in the commit message and changed query
predicate to t1.c3 = '00009' to make query select some rows.
------
Regards,
Alexander Korotkov
Supabase