Re: Instability in postgres_fdw regression tests

Hello Tom,

10.02.2026 19:06, Tom Lane wrote:
> Several BF animals have intermittently shown this regression diff:
>
> diff -U3 /home/bf/bf-build/culicidae/HEAD/pgsql/contrib/postgres_fdw/expected/postgres_fdw.out /home/bf/bf-build/culicidae/HEAD/pgsql.build/testrun/postgres_fdw-running/regress/results/postgres_fdw.out
> --- /home/bf/bf-build/culicidae/HEAD/pgsql/contrib/postgres_fdw/expected/postgres_fdw.out 2025-12-29 19:48:22.661603936 +0100
> +++ /home/bf/bf-build/culicidae/HEAD/pgsql.build/testrun/postgres_fdw-running/regress/results/postgres_fdw.out 2026-02-10 00:31:31.856460156 +0100
> @@ -6519,6 +6519,7 @@
> UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
> c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
> ------+----+-----+----+----+----+------------+----
> + 2010 | 0 | bar | | | | ft2 |
> 2001 | 1 | bar | | | | ft2 |
> 2002 | 2 | bar | | | | ft2 |
> 2003 | 3 | bar | | | | ft2 |
> @@ -6528,7 +6529,6 @@
> 2007 | 7 | bar | | | | ft2 |
> 2008 | 8 | bar | | | | ft2 |
> 2009 | 9 | bar | | | | ft2 |
> - 2010 | 0 | bar | | | | ft2 |
> (10 rows)
>
> EXPLAIN (verbose, costs off)
>
> The above is from culicidae [1], and greenfly has shown it a few times
> [2], and here's one from scorpion [3], and crake on a back branch [4],
> and there are a few more in the past 90 days.

FWIW, all the failures of this ilk are tracked at [1].

> It's pretty clear what is happening: the rows we are looking at are
> being returned by a seqscan, and they were just inserted a few lines
> above into a table that has been modified multiple times already.
> So the test is reliant on them being inserted in sequence at the
> end of the table, yet sometimes the last row is going into free
> space someplace earlier.
>
> One's first instinct is to blame autovacuum, but the test already
> goes out of its way to disable that:
>
> -- Disable autovacuum for these tables to avoid unexpected effects of that
> ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');

I reproduced and reported the failure before, please look at [2] —
probably some information there could be helpful...

[1] https://wiki.postgresql.org/wiki/Known_Buildfarm_Test_Failures#postgres_fdw.sql_might_fail_due_to_autovacuum
[2] https://www.postgresql.org/message-id/flat/867266ef-3dd1-44a9-a203-27cb5d2be58d%40gmail.com

Best regards,
Alexander