Query to identify all collations in the current database that need to be refreshed

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: carl(dot)witt(at)knime(dot)com
Subject: Query to identify all collations in the current database that need to be refreshed
Date: 2025-06-10 11:30:43
Message-ID: 174955504341.789.12003037401879382460@wrigleys.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/sql-altercollation.html
Description:

On the alter collation docs [1], it says "The following query can be used to
identify all collations in the current database that need to be refreshed
and the objects that depend on them"
```
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;
```
This seems to be a bit optimistic, since in my postgres instance, the
`default` collation has `collversion` set to `NULL` which would not pass the
comparison. In addition, dependencies to `default` do not seem to be
encoded, as
```
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
```
returns an empty row set. It seems like the description of the query is
inaccurate.
[1]
https://www.postgresql.org/docs/current/sql-altercollation.html#SQL-ALTERCOLLATION-NOTES

Browse pgsql-docs by date

  From Date Subject
Next Message Fujii Masao 2025-06-11 03:50:44 Re: Add missing references to database object statistics manipulation functions in documentation
Previous Message PG Doc comments form 2025-06-10 08:32:40 ALTER DATABASE lacks documentation of REFRESH COLLATION VERSION