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.
Attachments:
test_extensions-sql.diffapplication/octet-stream; name=test_extensions-sql.diffDownload
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 642c82ff5d..ea47f607ca 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -1,3 +1,18 @@
+CREATE OR REPLACE FUNCTION dx_plus(schema_name char varying) RETURNS TABLE("Object description" char varying) AS
+$BODY$
+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
+$BODY$
+LANGUAGE plpgsql;
+
CREATE SCHEMA has$dollar;
-- test some errors
@@ -24,9 +39,9 @@ CREATE EXTENSION test_ext6;
-- this table will be absorbed into test_ext7
create table old_table1 (col1 serial primary key);
create extension test_ext7;
-\dx+ test_ext7
+SELECT * FROM dx_plus('test_ext7');
alter extension test_ext7 update to '2.0';
-\dx+ test_ext7
+SELECT * FROM dx_plus('test_ext7');
-- test handling of temp objects created by extensions
create extension test_ext8;
@@ -62,22 +77,23 @@ begin
end';
-- extension should now contain no temp objects
-\dx+ test_ext8
+SELECT * FROM dx_plus('test_ext8');
-- dropping it should still work
drop extension test_ext8;
+
-- check handling of types as extension members
create extension test_ext9;
-\dx+ test_ext9
+SELECT * FROM dx_plus('test_ext9');
alter extension test_ext9 drop type varbitrange;
-\dx+ test_ext9
+SELECT * FROM dx_plus('test_ext9');
alter extension test_ext9 add type varbitrange;
-\dx+ test_ext9
+SELECT * FROM dx_plus('test_ext9');
alter extension test_ext9 drop table sometable;
-\dx+ test_ext9
+SELECT * FROM dx_plus('test_ext9');
alter extension test_ext9 add table sometable;
-\dx+ test_ext9
+SELECT * FROM dx_plus('test_ext9');
drop extension test_ext9;
-- Test creation of extension in temporary schema with two-phase commit,
@@ -165,7 +181,7 @@ SELECT 'x'::test_ext_type;
SELECT point(0,0) <<@@ polygon(circle(point(0,0),1));
-\dx+ test_ext_cor
+SELECT * FROM dx_plus('test_ext_cor');
--
-- CREATE IF NOT EXISTS is an entirely unsound thing for an extension
@@ -219,11 +235,11 @@ DROP TABLE ext_cine_tab2;
CREATE EXTENSION test_ext_cine;
-\dx+ test_ext_cine
+SELECT * FROM dx_plus('test_ext_cine');
ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
-\dx+ test_ext_cine
+SELECT * FROM dx_plus('test_ext_cine');
--
-- Test @extschema@ syntax.
@@ -239,7 +255,7 @@ CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE EXTENSION test_ext_set_schema SCHEMA s1;
ALTER EXTENSION test_ext_set_schema SET SCHEMA s2;
-\dx+ test_ext_set_schema
+SELECT * FROM dx_plus('test_ext_set_schema');
\sf s2.ess_func(int)
--
test_extensions-out.diffapplication/octet-stream; name=test_extensions-out.diffDownload
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index f357cc21aa..297556b88a 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -1,3 +1,17 @@
+CREATE OR REPLACE FUNCTION dx_plus(schema_name char varying) RETURNS TABLE("Object description" char varying) AS
+$BODY$
+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
+$BODY$
+LANGUAGE plpgsql;
CREATE SCHEMA has$dollar;
-- test some errors
CREATE EXTENSION test_ext1;
@@ -43,33 +57,31 @@ CREATE EXTENSION test_ext6;
-- this table will be absorbed into test_ext7
create table old_table1 (col1 serial primary key);
create extension test_ext7;
-\dx+ test_ext7
-Objects in extension "test_ext7"
+SELECT * FROM dx_plus('test_ext7');
Object description
-------------------------------
- sequence ext7_table1_col1_seq
- sequence ext7_table2_col2_seq
- sequence old_table1_col1_seq
- table ext7_table1
- table ext7_table2
+ type old_table1
table old_table1
type ext7_table1
- type ext7_table1[]
type ext7_table2
- type ext7_table2[]
- type old_table1
+ table ext7_table1
+ table ext7_table2
type old_table1[]
+ type ext7_table1[]
+ type ext7_table2[]
+ sequence old_table1_col1_seq
+ sequence ext7_table1_col1_seq
+ sequence ext7_table2_col2_seq
(12 rows)
alter extension test_ext7 update to '2.0';
-\dx+ test_ext7
-Objects in extension "test_ext7"
+SELECT * FROM dx_plus('test_ext7');
Object description
-------------------------------
- sequence ext7_table2_col2_seq
- table ext7_table2
type ext7_table2
+ table ext7_table2
type ext7_table2[]
+ sequence ext7_table2_col2_seq
(4 rows)
-- test handling of temp objects created by extensions
@@ -129,122 +141,116 @@ begin
raise log ''test_extensions looped % times'', c;
end';
-- extension should now contain no temp objects
-\dx+ test_ext8
-Objects in extension "test_ext8"
+SELECT * FROM dx_plus('test_ext8');
Object description
----------------------------
- function ext8_even(posint)
- table ext8_table1
- type ext8_table1
- type ext8_table1[]
type posint
type posint[]
+ type ext8_table1
+ table ext8_table1
+ type ext8_table1[]
+ function ext8_even(posint)
(6 rows)
-- dropping it should still work
drop extension test_ext8;
-- check handling of types as extension members
create extension test_ext9;
-\dx+ test_ext9
- Objects in extension "test_ext9"
+SELECT * FROM dx_plus('test_ext9');
Object description
----------------------------------------------------
- cast from varbitrange to varbitmultirange
- function varbitmultirange()
- function varbitmultirange(varbitrange)
- function varbitmultirange(varbitrange[])
- function varbitrange(bit varying,bit varying)
- function varbitrange(bit varying,bit varying,text)
+ type sometable
table sometable
+ type sometable[]
+ type varbitrange
type somecomposite
+ type varbitrange[]
type somecomposite[]
- type sometable
- type sometable[]
type varbitmultirange
type varbitmultirange[]
- type varbitrange
- type varbitrange[]
+ function varbitmultirange()
+ function varbitmultirange(varbitrange)
+ function varbitmultirange(varbitrange[])
+ cast from varbitrange to varbitmultirange
+ function varbitrange(bit varying,bit varying)
+ function varbitrange(bit varying,bit varying,text)
(15 rows)
alter extension test_ext9 drop type varbitrange;
-\dx+ test_ext9
- Objects in extension "test_ext9"
+SELECT * FROM dx_plus('test_ext9');
Object description
----------------------------------------------------
- cast from varbitrange to varbitmultirange
+ type sometable
+ table sometable
+ type sometable[]
+ type somecomposite
+ type somecomposite[]
function varbitmultirange()
function varbitmultirange(varbitrange)
function varbitmultirange(varbitrange[])
+ cast from varbitrange to varbitmultirange
function varbitrange(bit varying,bit varying)
function varbitrange(bit varying,bit varying,text)
- table sometable
- type somecomposite
- type somecomposite[]
- type sometable
- type sometable[]
(11 rows)
alter extension test_ext9 add type varbitrange;
-\dx+ test_ext9
- Objects in extension "test_ext9"
+SELECT * FROM dx_plus('test_ext9');
Object description
----------------------------------------------------
- cast from varbitrange to varbitmultirange
- function varbitmultirange()
- function varbitmultirange(varbitrange)
- function varbitmultirange(varbitrange[])
- function varbitrange(bit varying,bit varying)
- function varbitrange(bit varying,bit varying,text)
+ type sometable
table sometable
+ type sometable[]
+ type varbitrange
type somecomposite
+ type varbitrange[]
type somecomposite[]
- type sometable
- type sometable[]
type varbitmultirange
type varbitmultirange[]
- type varbitrange
- type varbitrange[]
+ function varbitmultirange()
+ function varbitmultirange(varbitrange)
+ function varbitmultirange(varbitrange[])
+ cast from varbitrange to varbitmultirange
+ function varbitrange(bit varying,bit varying)
+ function varbitrange(bit varying,bit varying,text)
(15 rows)
alter extension test_ext9 drop table sometable;
-\dx+ test_ext9
- Objects in extension "test_ext9"
+SELECT * FROM dx_plus('test_ext9');
Object description
----------------------------------------------------
- cast from varbitrange to varbitmultirange
+ type varbitrange
+ type somecomposite
+ type varbitrange[]
+ type somecomposite[]
+ type varbitmultirange
+ type varbitmultirange[]
function varbitmultirange()
function varbitmultirange(varbitrange)
function varbitmultirange(varbitrange[])
+ cast from varbitrange to varbitmultirange
function varbitrange(bit varying,bit varying)
function varbitrange(bit varying,bit varying,text)
- type somecomposite
- type somecomposite[]
- type varbitmultirange
- type varbitmultirange[]
- type varbitrange
- type varbitrange[]
(12 rows)
alter extension test_ext9 add table sometable;
-\dx+ test_ext9
- Objects in extension "test_ext9"
+SELECT * FROM dx_plus('test_ext9');
Object description
----------------------------------------------------
- cast from varbitrange to varbitmultirange
- function varbitmultirange()
- function varbitmultirange(varbitrange)
- function varbitmultirange(varbitrange[])
- function varbitrange(bit varying,bit varying)
- function varbitrange(bit varying,bit varying,text)
+ type sometable
table sometable
+ type sometable[]
+ type varbitrange
type somecomposite
+ type varbitrange[]
type somecomposite[]
- type sometable
- type sometable[]
type varbitmultirange
type varbitmultirange[]
- type varbitrange
- type varbitrange[]
+ function varbitmultirange()
+ function varbitmultirange(varbitrange)
+ function varbitmultirange(varbitrange[])
+ cast from varbitrange to varbitmultirange
+ function varbitrange(bit varying,bit varying)
+ function varbitrange(bit varying,bit varying,text)
(15 rows)
drop extension test_ext9;
@@ -357,17 +363,16 @@ SELECT point(0,0) <<@@ polygon(circle(point(0,0),1));
t
(1 row)
-\dx+ test_ext_cor
-Objects in extension "test_ext_cor"
+SELECT * FROM dx_plus('test_ext_cor');
Object description
------------------------------
- function ext_cor_func()
- operator <<@@(point,polygon)
type ext_cor_view
- type ext_cor_view[]
+ view ext_cor_view
type test_ext_type
+ type ext_cor_view[]
type test_ext_type[]
- view ext_cor_view
+ function ext_cor_func()
+ operator <<@@(point,polygon)
(7 rows)
--
@@ -412,48 +417,46 @@ ERROR: table ext_cine_tab2 is not a member of extension "test_ext_cine"
DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns.
DROP TABLE ext_cine_tab2;
CREATE EXTENSION test_ext_cine;
-\dx+ test_ext_cine
-Objects in extension "test_ext_cine"
+SELECT * FROM dx_plus('test_ext_cine');
Object description
-----------------------------------
- collation ext_cine_coll
- foreign-data wrapper ext_cine_fdw
- materialized view ext_cine_mv
- schema ext_cine_schema
- sequence ext_cine_seq
- server ext_cine_srv
- table ext_cine_tab1
- table ext_cine_tab2
type ext_cine_mv
type ext_cine_mv[]
type ext_cine_tab1
- type ext_cine_tab1[]
type ext_cine_tab2
+ server ext_cine_srv
+ table ext_cine_tab1
+ table ext_cine_tab2
+ type ext_cine_tab1[]
type ext_cine_tab2[]
+ sequence ext_cine_seq
+ schema ext_cine_schema
+ collation ext_cine_coll
+ materialized view ext_cine_mv
+ foreign-data wrapper ext_cine_fdw
(14 rows)
ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
-\dx+ test_ext_cine
-Objects in extension "test_ext_cine"
+SELECT * FROM dx_plus('test_ext_cine');
Object description
-----------------------------------
- collation ext_cine_coll
- foreign-data wrapper ext_cine_fdw
- materialized view ext_cine_mv
- schema ext_cine_schema
- sequence ext_cine_seq
+ type ext_cine_mv
+ type ext_cine_mv[]
+ type ext_cine_tab1
+ type ext_cine_tab2
+ type ext_cine_tab3
server ext_cine_srv
table ext_cine_tab1
table ext_cine_tab2
table ext_cine_tab3
- type ext_cine_mv
- type ext_cine_mv[]
- type ext_cine_tab1
type ext_cine_tab1[]
- type ext_cine_tab2
type ext_cine_tab2[]
- type ext_cine_tab3
type ext_cine_tab3[]
+ sequence ext_cine_seq
+ schema ext_cine_schema
+ collation ext_cine_coll
+ materialized view ext_cine_mv
+ foreign-data wrapper ext_cine_fdw
(17 rows)
--
@@ -470,26 +473,25 @@ CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE EXTENSION test_ext_set_schema SCHEMA s1;
ALTER EXTENSION test_ext_set_schema SET SCHEMA s2;
-\dx+ test_ext_set_schema
- Objects in extension "test_ext_set_schema"
+SELECT * FROM dx_plus('test_ext_set_schema');
Object description
-------------------------------------------------------
- cast from s2.ess_range_type to s2.ess_multirange_type
- function s2.ess_func(integer)
- function s2.ess_multirange_type()
- function s2.ess_multirange_type(s2.ess_range_type)
- function s2.ess_multirange_type(s2.ess_range_type[])
- function s2.ess_range_type(text,text)
- function s2.ess_range_type(text,text,text)
+ type s2.ess_table
table s2.ess_table
+ type s2.ess_table[]
+ type s2.ess_range_type
+ type s2.ess_range_type[]
type s2.ess_composite_type
- type s2.ess_composite_type[]
type s2.ess_multirange_type
+ type s2.ess_composite_type[]
+ function s2.ess_func(integer)
type s2.ess_multirange_type[]
- type s2.ess_range_type
- type s2.ess_range_type[]
- type s2.ess_table
- type s2.ess_table[]
+ function s2.ess_multirange_type()
+ function s2.ess_range_type(text,text)
+ function s2.ess_range_type(text,text,text)
+ function s2.ess_multirange_type(s2.ess_range_type)
+ function s2.ess_multirange_type(s2.ess_range_type[])
+ cast from s2.ess_range_type to s2.ess_multirange_type
(16 rows)
\sf s2.ess_func(int)
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.fakeev@postgrespro.ru>
Sent: Tuesday, July 2, 2024 2:39 PM
To: pgsql-hackers@postgresql.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.
Attachments:
Makefile.diffapplication/octet-stream; name=Makefile.diffDownload
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index 05272e6a40..8e5849ebdf 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -27,8 +27,6 @@ DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
REGRESS = test_extensions test_extdepend
-# force C locale for output stability
-NO_LOCALE = 1
ifdef USE_PGXS
PG_CONFIG = pg_config
meson.build.diffapplication/octet-stream; name=meson.build.diffDownload
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index c5f3424da5..94eeab480b 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -53,6 +53,5 @@ tests += {
'test_extensions',
'test_extdepend',
],
- 'regress_args': ['--no-locale'],
},
}