From c5240d690af1d8ee17b84d2d9235800869a0cee7 Mon Sep 17 00:00:00 2001 From: Jeff Davis Date: Sat, 29 Oct 2022 14:31:31 -0700 Subject: [PATCH] Add views: pg_collation_versions and pg_collation_dependencies. These new views are useful to understand when a collation version changes, and which dependent objects may be affected. --- doc/src/sgml/system-views.sgml | 166 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 48 ++++++++ src/test/regress/expected/rules.out | 50 ++++++++ 3 files changed, 264 insertions(+) diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 1ca7c3f9bf..cf48073d2f 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -549,6 +549,172 @@ + + <structname>pg_collation_dependencies</structname> + + + pg_collation_dependencies + + + + The view pg_collation_dependencies shows the + dependencies (including transitive dependencies) of collations. It is + intended to be used to understand the potential impact of a change to a + collation due, for example, to a change in the collation provider library. + + + + By default, the pg_collation_dependencies view can + be read only by superusers. + + + + <structname>pg_collation_dependencies</structname> Columns + + + + + Column Type + + + Description + + + + + + + + classid oid + (references pg_class.oid) + + + The OID of the system catalog the dependent object is in + + + + + + objid oid + (references any OID column) + + + The OID of the specific dependent object + + + + + + objsubid int4 + + + For a table column, this is the column number (the + objid and classid refer to the + table itself). For all other object types, this column is + zero. + + + + + + refcollid regcollation + + + The collation on which the object depends. + + + + + + description text + + + The description of the dependent object. + + + + +
+ + + + + <structname>pg_collation_versions</structname> + + + pg_collation_dependencies + + + + The view pg_collation_versions shows the current + version as well as the actual version (obtained from the collation + provider) for each collation. It is intended to detect changes in the + collation provider library. + + + + By default, the pg_collation_versions view can be + read only by superusers. + + + + <structname>pg_collation_versions</structname> Columns + + + + + Column Type + + + Description + + + + + + + + collation_name name + + + The collation name + + + + + + provider text + + + The collation provider (default, + libc, or icu). + + + + + version text + + + The version string in the catalog, i.e. the version at the time the + collation or database was created, or the version last refreshed. + + + + + + version text + + + The current version string obtained from the collation provider library + for the collation's locale name. + + + + +
+ + + <structname>pg_config</structname> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 2d8104b090..a283d9a3c6 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1313,3 +1313,51 @@ CREATE VIEW pg_stat_subscription_stats AS ss.stats_reset FROM pg_subscription as s, pg_stat_get_subscription_stats(s.oid) as ss; + +CREATE VIEW pg_collation_versions AS + SELECT + collname AS collation_name, + CASE collprovider + WHEN 'd' THEN 'default' + WHEN 'c' THEN 'libc' + WHEN 'i' THEN 'icu' + END AS provider, + CASE WHEN oid = 'default'::regcollation THEN + (SELECT datcollversion FROM pg_database + WHERE datname = current_database()) + ELSE collversion + END AS version, + pg_collation_actual_version(oid) AS actual_version + FROM pg_collation; + +CREATE OR REPLACE VIEW pg_collation_dependencies AS + WITH RECURSIVE + collation_dependencies(classid, objid, objsubid, refcollid) AS ( + select d.classid, d.objid, d.objsubid, + refobjid::regcollation as refcollid + from pg_depend d + where refclassid='pg_collation'::regclass + union + select d.classid, d.objid, d.objsubid, cd.refcollid + from pg_depend d, collation_dependencies cd + where cd.classid = d.refclassid + and cd.objid = d.refobjid + and cd.objsubid = d.refobjsubid + ) + SELECT cd.classid, cd.objid, cd.objsubid, cd.refcollid, + pg_describe_object(cd.classid, cd.objid, cd.objsubid) as description + FROM collation_dependencies cd + WHERE + -- ignore system objects + cd.objid >= 16384 + AND CASE WHEN cd.classid = 'pg_class'::regclass THEN + -- ignore TOAST tables + (SELECT relkind <> 't' FROM pg_class c WHERE cd.objid = c.oid) + WHEN cd.classid = 'pg_trigger'::regclass THEN + -- ignore array types + (SELECT NOT tgisinternal FROM pg_trigger tg WHERE cd.objid = tg.oid) + WHEN cd.classid = 'pg_type'::regclass THEN + -- ignore array types + (SELECT typelem = 0 FROM pg_type t WHERE cd.objid = t.oid) + ELSE TRUE + END; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index bfcd8ac9a0..93fc91a4e2 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1312,6 +1312,56 @@ pg_backend_memory_contexts| SELECT pg_get_backend_memory_contexts.name, pg_get_backend_memory_contexts.free_chunks, pg_get_backend_memory_contexts.used_bytes FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes); +pg_collation_dependencies| WITH RECURSIVE collation_dependencies(classid, objid, objsubid, refcollid) AS ( + SELECT d.classid, + d.objid, + d.objsubid, + (d.refobjid)::regcollation AS refcollid + FROM pg_depend d + WHERE (d.refclassid = ('pg_collation'::regclass)::oid) + UNION + SELECT d.classid, + d.objid, + d.objsubid, + cd_1.refcollid + FROM pg_depend d, + collation_dependencies cd_1 + WHERE ((cd_1.classid = d.refclassid) AND (cd_1.objid = d.refobjid) AND (cd_1.objsubid = d.refobjsubid)) + ) + SELECT cd.classid, + cd.objid, + cd.objsubid, + cd.refcollid, + pg_describe_object(cd.classid, cd.objid, cd.objsubid) AS description + FROM collation_dependencies cd + WHERE ((cd.objid >= (16384)::oid) AND + CASE + WHEN (cd.classid = ('pg_class'::regclass)::oid) THEN ( SELECT (c.relkind <> 't'::"char") + FROM pg_class c + WHERE (cd.objid = c.oid)) + WHEN (cd.classid = ('pg_trigger'::regclass)::oid) THEN ( SELECT (NOT tg.tgisinternal) + FROM pg_trigger tg + WHERE (cd.objid = tg.oid)) + WHEN (cd.classid = ('pg_type'::regclass)::oid) THEN ( SELECT (t.typelem = (0)::oid) + FROM pg_type t + WHERE (cd.objid = t.oid)) + ELSE true + END); +pg_collation_versions| SELECT pg_collation.collname AS collation_name, + CASE pg_collation.collprovider + WHEN 'd'::"char" THEN 'default'::text + WHEN 'c'::"char" THEN 'libc'::text + WHEN 'i'::"char" THEN 'icu'::text + ELSE NULL::text + END AS provider, + CASE + WHEN (pg_collation.oid = ('"default"'::regcollation)::oid) THEN ( SELECT pg_database.datcollversion + FROM pg_database + WHERE (pg_database.datname = current_database())) + ELSE pg_collation.collversion + END AS version, + pg_collation_actual_version(pg_collation.oid) AS actual_version + FROM pg_collation; pg_config| SELECT pg_config.name, pg_config.setting FROM pg_config() pg_config(name, setting); -- 2.34.1