Content-Type: multipart/mixed; boundary="------------fjYOrUFg5Fl7i9B1IcpNnEG8"
Message-ID: <ff736830-eaa9-4c6e-9bf5-99d817cbe02e@gmail.com>
Date: Fri, 3 Apr 2026 08:42:58 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 dgustafsson@postgresql.org
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
Subject: Flaky test in t/100_vacuumdb.pl: ordering assumption not stable under
 plan changes
Archived-At: 
 <https://www.postgresql.org/message-id/ff736830-eaa9-4c6e-9bf5-99d817cbe02e%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------fjYOrUFg5Fl7i9B1IcpNnEG8
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------fjYOrUFg5Fl7i9B1IcpNnEG8
Content-Type: text/plain; charset=UTF-8;
 name="0001-Fix-flaky-ordering-assertion-in-t-100_vacuumdb.pl.patch"
Content-Disposition: attachment;
 filename*0="0001-Fix-flaky-ordering-assertion-in-t-100_vacuumdb.pl.patch"
Content-Transfer-Encoding: base64

RnJvbSBmOTdlM2NhZTcwOWY5MjE1NzliMjY1ZDkzMDczMjhlOGEyNTE1YjQ3IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmVpIFYuIExlcGlraG92IiA8bGVwaWhvdkBn
bWFpbC5jb20+CkRhdGU6IEZyaSwgMyBBcHIgMjAyNiAwODozNjozMyArMDIwMApTdWJqZWN0
OiBbUEFUQ0hdIEZpeCBmbGFreSBvcmRlcmluZyBhc3NlcnRpb24gaW4gdC8xMDBfdmFjdXVt
ZGIucGwKCkJvdGggdGVzdCB0YWJsZXMgaGF2ZSByZWxwYWdlcz0wLCBtYWtpbmcgT1JERVIg
QlkgYy5yZWxwYWdlcyBERVNDCnVuc3RhYmxlLiAgUmVwbGFjZSB0aGUgc2VxdWVudGlhbCBy
ZWdleCB3aXRoIG9yZGVyLWluZGVwZW5kZW50Cmxvb2thaGVhZHMuCi0tLQogc3JjL2Jpbi9z
Y3JpcHRzL3QvMTAwX3ZhY3V1bWRiLnBsIHwgNCArKy0tCiAxIGZpbGUgY2hhbmdlZCwgMiBp
bnNlcnRpb25zKCspLCAyIGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iaW4vc2Ny
aXB0cy90LzEwMF92YWN1dW1kYi5wbCBiL3NyYy9iaW4vc2NyaXB0cy90LzEwMF92YWN1dW1k
Yi5wbAppbmRleCBjZjBiODUzZTliZi4uNDBmODdmZTExZWMgMTAwNjQ0Ci0tLSBhL3NyYy9i
aW4vc2NyaXB0cy90LzEwMF92YWN1dW1kYi5wbAorKysgYi9zcmMvYmluL3NjcmlwdHMvdC8x
MDBfdmFjdXVtZGIucGwKQEAgLTE3NSw4ICsxNzUsOCBAQCAkbm9kZS0+aXNzdWVzX3NxbF91
bmxpa2UoCiAJJ3ZhY3V1bWRiIC0tZHJ5LXJ1bicpOwogJG5vZGUtPmlzc3Vlc19zcWxfbGlr
ZSgKIAlbICd2YWN1dW1kYicsICctLXNjaGVtYScgPT4gJyJGb28iJywgJy0tc2NoZW1hJyA9
PiAnIkJhciInLCAncG9zdGdyZXMnIF0sCi0JcXIvVkFDVVVNXCBcKFNLSVBfREFUQUJBU0Vf
U1RBVFNcKVwgIkZvbyIuYmFyCi0JCS4qVkFDVVVNXCBcKFNLSVBfREFUQUJBU0VfU1RBVFNc
KVwgIkJhciIuYmF6CisJcXIvKD89LipWQUNVVU1cIFwoU0tJUF9EQVRBQkFTRV9TVEFUU1wp
XCAiRm9vIlwuYmFyKQorCSAgICg/PS4qVkFDVVVNXCBcKFNLSVBfREFUQUJBU0VfU1RBVFNc
KVwgIkJhciJcLmJheikKIAkvc3gsCiAJJ3ZhY3V1bWRiIG11bHRpcGxlIC0tc2NoZW1hIHN3
aXRjaGVzJyk7CiAkbm9kZS0+aXNzdWVzX3NxbF9saWtlKAotLSAKMi41My4wCgo=

--------------fjYOrUFg5Fl7i9B1IcpNnEG8--