Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.2\))
Subject: Re: Flaky test in t/100_vacuumdb.pl: ordering assumption not stable
 under plan changes
From: Daniel Gustafsson <daniel@yesql.se>
In-Reply-To: <ff736830-eaa9-4c6e-9bf5-99d817cbe02e@gmail.com>
Date: Fri, 3 Apr 2026 12:27:27 +0200
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <EC601C7D-FB13-49F2-8F37-59CA9B61B3FD@yesql.se>
References: <ff736830-eaa9-4c6e-9bf5-99d817cbe02e@gmail.com>
To: Andrei Lepikhov <lepihov@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/EC601C7D-FB13-49F2-8F37-59CA9B61B3FD%40yesql.se>
Precedence: bulk

> On 3 Apr 2026, at 08:42, Andrei Lepikhov <lepihov@gmail.com> wrote:
>=20
> Hi,
>=20
> I found that t/100_vacuumdb.pl has a fragile ordering check that fails =
if the query plan for vacuumdb's catalogue query changes. I sometimes =
see how this test fails when writing an optimisation-related extension.
>=20
> The test checks that vacuumdb processes "Foo".bar before "Bar".baz:
>=20
>    qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
>        .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
>    /sx,
>=20
> Both tables being tested, "Foo".bar and "Bar".baz, are created empty. =
This means pg_class.relpages is 0 for both and the sort order is =
completely unstable. The output order depends entirely on which query =
plan will be chosen. Any change in the planner that affects the plan for =
this query, such as a new join path type or a cost model change, may =
flip the order and cause the test to fail.
>=20
> AFAICS, The fix is quite trivial. Change the test regex (in =
100_vacuumdb.pl) to use order-independent lookaheads instead of a =
sequential match:
>=20
>       qr/(?=3D.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo"\.bar)
>          (?=3D.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar"\.baz)
>       /sx,
>=20
> This makes the test robust regardless of the order in which the server =
returns results.
>=20
> Hence, it doesn=E2=80=99t change anything important. I think it =
deserves to be back-patched down to v.16 (like the commit 2143d96dc7b =
introduced this test) so other extensions can be stable with check-world =
tests.

Thanks for the report, I'll have a look.

--
Daniel Gustafsson