public inbox for [email protected]
help / color / mirror / Atom feedFlaky test in t/100_vacuumdb.pl: ordering assumption not stable under plan changes
2+ messages / 2 participants
[nested] [flat]
* Flaky test in t/100_vacuumdb.pl: ordering assumption not stable under plan changes
@ 2026-04-03 06:42 Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Andrei Lepikhov @ 2026-04-03 06:42 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; [email protected]
Hi,
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.
The test checks that vacuumdb processes "Foo".bar before "Bar".baz:
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
/sx,
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.
AFAICS, The fix is quite trivial. Change the test regex (in
100_vacuumdb.pl) to use order-independent lookaheads instead of a
sequential match:
qr/(?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo"\.bar)
(?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar"\.baz)
/sx,
This makes the test robust regardless of the order in which the server
returns results.
Hence, it doesn’t 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.
Regards,
Andrei Lepikhov
pgEdge
From f97e3cae709f921579b265d9307328e8a2515b47 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Fri, 3 Apr 2026 08:36:33 +0200
Subject: [PATCH] Fix flaky ordering assertion in t/100_vacuumdb.pl
Both test tables have relpages=0, making ORDER BY c.relpages DESC
unstable. Replace the sequential regex with order-independent
lookaheads.
---
src/bin/scripts/t/100_vacuumdb.pl | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index cf0b853e9bf..40f87fe11ec 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -175,8 +175,8 @@ $node->issues_sql_unlike(
'vacuumdb --dry-run');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
- qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
- .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
+ qr/(?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo"\.bar)
+ (?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar"\.baz)
/sx,
'vacuumdb multiple --schema switches');
$node->issues_sql_like(
--
2.53.0
Attachments:
[text/plain] 0001-Fix-flaky-ordering-assertion-in-t-100_vacuumdb.pl.patch (1.1K, 2-0001-Fix-flaky-ordering-assertion-in-t-100_vacuumdb.pl.patch)
download | inline diff:
From f97e3cae709f921579b265d9307328e8a2515b47 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Fri, 3 Apr 2026 08:36:33 +0200
Subject: [PATCH] Fix flaky ordering assertion in t/100_vacuumdb.pl
Both test tables have relpages=0, making ORDER BY c.relpages DESC
unstable. Replace the sequential regex with order-independent
lookaheads.
---
src/bin/scripts/t/100_vacuumdb.pl | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index cf0b853e9bf..40f87fe11ec 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -175,8 +175,8 @@ $node->issues_sql_unlike(
'vacuumdb --dry-run');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
- qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
- .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
+ qr/(?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo"\.bar)
+ (?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar"\.baz)
/sx,
'vacuumdb multiple --schema switches');
$node->issues_sql_like(
--
2.53.0
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Flaky test in t/100_vacuumdb.pl: ordering assumption not stable under plan changes
@ 2026-04-03 10:27 Daniel Gustafsson <[email protected]>
parent: Andrei Lepikhov <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Daniel Gustafsson @ 2026-04-03 10:27 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
> On 3 Apr 2026, at 08:42, Andrei Lepikhov <[email protected]> wrote:
>
> Hi,
>
> 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.
>
> The test checks that vacuumdb processes "Foo".bar before "Bar".baz:
>
> qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
> .*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar".baz
> /sx,
>
> 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.
>
> AFAICS, The fix is quite trivial. Change the test regex (in 100_vacuumdb.pl) to use order-independent lookaheads instead of a sequential match:
>
> qr/(?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo"\.bar)
> (?=.*VACUUM\ \(SKIP_DATABASE_STATS\)\ "Bar"\.baz)
> /sx,
>
> This makes the test robust regardless of the order in which the server returns results.
>
> Hence, it doesn’t 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-03 10:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-03 06:42 Flaky test in t/100_vacuumdb.pl: ordering assumption not stable under plan changes Andrei Lepikhov <[email protected]>
2026-04-03 10:27 ` Daniel Gustafsson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox