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