Database-level collation version tracking

This adds to database objects the same version tracking that collation
objects have.  There is a new pg_database column datcollversion that
stores the version, a new function
pg_database_collation_actual_version() to get the version from the
operating system, and a new subcommand ALTER DATABASE ... REFRESH
COLLATION VERSION.

This was not originally added together with pg_collation.collversion,
since originally version tracking was only supported for ICU, and ICU
on a database-level is not currently supported.  But we now have
version tracking for glibc (since PG13), FreeBSD (since PG14), and
Windows (since PG13), so this is useful to have now.

Reviewed-by: Julien Rouhaud <rjuju123@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/f0ff3190-29a3-5b39-a179-fa32eee57db6%40enterprisedb.com

5 files changed, 64 insertions, 1 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 879d2dbce03..5a1627a3941 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3045,6 +3045,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
 
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>datcollversion</structfield> <type>text</type>
+      </para>
+      <para>
+       Provider-specific version of the collation.  This is recorded when the
+       database is created and then checked when it is used, to detect
+       changes in the collation definition that could lead to data corruption.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>datacl</structfield> <type>aclitem[]</type>
       </para>
       <para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1b064b4febf..df3cd5987b7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27064,6 +27064,24 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
+         <primary>pg_database_collation_actual_version</primary>
+        </indexterm>
+        <function>pg_database_collation_actual_version</function> ( <type>oid</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Returns the actual version of the database's collation as it is currently
+        installed in the operating system.  If this is different from the
+        value in
+        <structname>pg_database</structname>.<structfield>datcollversion</structfield>,
+        then objects depending on the collation might need to be rebuilt.  See
+        also <xref linkend="sql-alterdatabase"/>.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
          <primary>pg_import_system_collations</primary>
         </indexterm>
         <function>pg_import_system_collations</function> ( <parameter>schema</parameter> <type>regnamespace</type> )
diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml
index 892c4665655..a8c831d7286 100644
--- a/doc/src/sgml/ref/alter_collation.sgml
+++ b/doc/src/sgml/ref/alter_collation.sgml
@@ -151,7 +151,8 @@ HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg
    </para>
   </note>
   <para>
-   Currently, there is no version tracking for the database default collation.
+   For the database default collation, there is an analogous command
+   <literal>ALTER DATABASE ... REFRESH COLLATION VERSION</literal>.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml
index 81e37536a3f..89ed261b4c2 100644
--- a/doc/src/sgml/ref/alter_database.sgml
+++ b/doc/src/sgml/ref/alter_database.sgml
@@ -35,6 +35,8 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> OWNER TO { <rep
 
 ALTER DATABASE <replaceable class="parameter">name</replaceable> SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
 
+ALTER DATABASE <replaceable class="parameter">name</replaceable> REFRESH COLLATION VERSION
+
 ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
 ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
 ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>
@@ -171,6 +173,16 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET ALL
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>REFRESH COLLATION VERSION</literal></term>
+    <listitem>
+     <para>
+      Update the database collation version.  See <xref
+      linkend="sql-altercollation-notes"/> for background.
+     </para>
+    </listitem>
+   </varlistentry>
+
      <varlistentry>
       <term><replaceable>configuration_parameter</replaceable></term>
       <term><replaceable>value</replaceable></term>
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index f22e28dc81b..f70d0c75b4d 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -28,6 +28,7 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
            [ LOCALE [=] <replaceable class="parameter">locale</replaceable> ]
            [ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
            [ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
+           [ COLLATION_VERSION = <replaceable>collation_version</replaceable> ]
            [ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
            [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
            [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ]
@@ -158,6 +159,26 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
        </para>
       </listitem>
      </varlistentry>
+
+     <varlistentry>
+      <term><replaceable>collation_version</replaceable></term>
+
+      <listitem>
+       <para>
+        Specifies the collation version string to store with the database.
+        Normally, this should be omitted, which will cause the version to be
+        computed from the actual version of the database collation as provided
+        by the operating system.  This option is intended to be used by
+        <command>pg_upgrade</command> for copying the version from an existing
+        installation.
+       </para>
+
+       <para>
+        See also <xref linkend="sql-alterdatabase"/> for how to handle
+        database collation version mismatches.
+       </para>
+     </listitem>
+    </varlistentry>
      <varlistentry>
       <term><replaceable class="parameter">tablespace_name</replaceable></term>
       <listitem>