After a system upgrade there can be a problem with the collations:
Example:
$ psql
WARNING: database "postgres" has a collation version mismatch
DETAIL: The database was created using collation version 2.27, but the operating system provides version 2.35.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.2 (Ubuntu 15.2-1.pgdg18.04+1))
Type "help" for help.
Changing the database:
test=# \c test
WARNING: database "test" has a collation version mismatch
DETAIL: The database was created using collation version 2.27, but the operating system provides version 2.35.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE test REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
You are now connected to database "test" as user "postgres".
GLIBC of O/S:
$ ldd --version | grep LIBC
ldd (Ubuntu GLIBC 2.35-0ubuntu3.8) 2.35
$ /usr/lib/x86_64-linux-gnu/libc.so.6 --version | head -n 1
GNU C Library (Ubuntu GLIBC 2.35-0ubuntu3.8) stable release version 2.35.
GLIBC of PostgreSQL database???
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | | libc |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | | libc |
(4 rows)
Queries:
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2
;
SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX')
;
SELECT datname AS "Name", encoding AS "Encoding", datlocprovider AS "Locale Provider", datcollate AS "Collate", datctype AS "Ctype", daticulocale AS "ICU Locale", datcollversion AS "Collation Version"
FROM pg_database
WHERE datistemplate = false
;
Name | Encoding | Locale Provider | Collate | Ctype | ICU Locale | Collation Version
----------+----------+-----------------+-------------+-------------+------------+-------------------
postgres | 6 | c | en_GB.UTF-8 | en_GB.UTF-8 | | 2.35
test | 6 | c | en_GB.UTF-8 | en_GB.UTF-8 | | 2.35
(2 rows)
test=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE: changing version from 2.27 to 2.35
ALTER DATABASE
test=# ALTER DATABASE test REFRESH COLLATION VERSION;
NOTICE: changing version from 2.27 to 2.35
ALTER DATABASE