v1-0004-Add-views-pg_collation_versions-and-pg_collation_dep.patch

From c5240d690af1d8ee17b84d2d9235800869a0cee7 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jdavis@postgresql.org>
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 @@
   </para>
  </sect1>
 
+ <sect1 id="view-pg-collation-dependencies">
+  <title><structname>pg_collation_dependencies</structname></title>
+
+  <indexterm zone="view-pg-collation-dependencies">
+   <primary>pg_collation_dependencies</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_collation_dependencies</structname> 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.
+  </para>
+
+  <para>
+   By default, the <structname>pg_collation_dependencies</structname> view can
+   be read only by superusers.
+  </para>
+
+  <table>
+   <title><structname>pg_collation_dependencies</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>classid</structfield> <type>oid</type>
+       (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+      </para>
+      <para>
+       The OID of the system catalog the dependent object is in
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>objid</structfield> <type>oid</type>
+       (references any OID column)
+      </para>
+      <para>
+       The OID of the specific dependent object
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>objsubid</structfield> <type>int4</type>
+      </para>
+      <para>
+       For a table column, this is the column number (the
+       <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+       table itself).  For all other object types, this column is
+       zero.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>refcollid</structfield> <type>regcollation</type>
+      </para>
+      <para>
+       The collation on which the object depends.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>description</structfield> <type>text</type>
+      </para>
+      <para>
+       The description of the dependent object.
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-collation-versions">
+  <title><structname>pg_collation_versions</structname></title>
+
+  <indexterm zone="view-pg-collation-versions">
+   <primary>pg_collation_dependencies</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_collation_versions</structname> 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.
+  </para>
+
+  <para>
+   By default, the <structname>pg_collation_versions</structname> view can be
+   read only by superusers.
+  </para>
+
+  <table>
+   <title><structname>pg_collation_versions</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>collation_name</structfield> <type>name</type>
+      </para>
+      <para>
+       The collation name
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>provider</structfield> <type>text</type>
+      </para>
+      <para>
+       The collation provider (<literal>default</literal>,
+       <literal>libc</literal>, or <literal>icu</literal>).  </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>version</structfield> <type>text</type>
+      </para>
+      <para>
+       The version string in the catalog, i.e. the version at the time the
+       collation or database was created, or the version last refreshed.
+       </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>version</structfield> <type>text</type>
+      </para>
+      <para>
+       The current version string obtained from the collation provider library
+       for the collation's locale name.
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect1>
+
  <sect1 id="view-pg-config">
   <title><structname>pg_config</structname></title>
 
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