RE: Test_extensions installcheck fails with ICU provider, workaround

UPD:

With the proposed changes there is no need to set NO_LOCALE to configure
the test build, so the attached patches remove this option from the
recipes.

From: Aleksei Fakeev <a(dot)fakeev(at)postgrespro(dot)ru>
Sent: Tuesday, July 2, 2024 2:39 PM
To: pgsql-hackers(at)postgresql(dot)org
Subject: Test_extensions installcheck fails with ICU provider,
workaround

Hello there,

The test src/test/modules/test_extensions/sql/test_extensions.sql don't
pass during make installcheck on the builds with ICU support (configure
--with-icu) and with database created with this one's locale provider
(initdb --locale-provider=icu --icu-locale=en-US).

A root cause is unstable sorting order of the output of the psql command
\dx+, which is sorted according to the current locale rules, and then
compared to expected etalon in the test, which was sorted with different
provider rules.

Since the characters ')' and ',' have different orders when sorted by
the ICU provider and ASCII sorted (--no-locale), the following output
lines change their positions relative to each other:

"function varbitrange(bit varying,bit varying)"

"function varbitrange(bit varying,bit varying,text)"

I tried to use the \dx+ replacement function in this test with locale
provider-independent sorting, and it solves the problem:

CREATE OR REPLACE FUNCTION dx_plus(schema_name char varying) RETURNS
TABLE("Object description" char varying) AS

$$

BEGIN

RETURN QUERY SELECT obj_descr::varchar AS "Object
description" FROM (SELECT regexp_replace(pg_describe_object(classid,
objid, objsubid)

, 'pg_temp_\d+'

, 'pg_temp', 'g') AS obj_descr

FROM pg_depend

WHERE refclassid = 'pg_extension'::regclass

AND deptype = 'e'

AND refobjid = (SELECT oid FROM pg_extension WHERE
extname = schema_name))

ORDER BY length(obj_descr), obj_descr;

END;

$$

__

PS:

Patches for a master branch are attached.