Skip to content

Commit 37851a8

Browse files
peterepetere
committed
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
1 parent 9898c5e commit 37851a8

22 files changed

+367
-15
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3043,6 +3043,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
30433043
</para></entry>
30443044
</row>
30453045

3046+
<row>
3047+
<entry role="catalog_table_entry"><para role="column_definition">
3048+
<structfield>datcollversion</structfield> <type>text</type>
3049+
</para>
3050+
<para>
3051+
Provider-specific version of the collation. This is recorded when the
3052+
database is created and then checked when it is used, to detect
3053+
changes in the collation definition that could lead to data corruption.
3054+
</para></entry>
3055+
</row>
3056+
30463057
<row>
30473058
<entry role="catalog_table_entry"><para role="column_definition">
30483059
<structfield>datacl</structfield> <type>aclitem[]</type>

‎doc/src/sgml/func.sgml

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27061,6 +27061,24 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
2706127061
</para></entry>
2706227062
</row>
2706327063

27064+
<row>
27065+
<entry role="func_table_entry"><para role="func_signature">
27066+
<indexterm>
27067+
<primary>pg_database_collation_actual_version</primary>
27068+
</indexterm>
27069+
<function>pg_database_collation_actual_version</function> ( <type>oid</type> )
27070+
<returnvalue>text</returnvalue>
27071+
</para>
27072+
<para>
27073+
Returns the actual version of the database's collation as it is currently
27074+
installed in the operating system. If this is different from the
27075+
value in
27076+
<structname>pg_database</structname>.<structfield>datcollversion</structfield>,
27077+
then objects depending on the collation might need to be rebuilt. See
27078+
also <xref linkend="sql-alterdatabase"/>.
27079+
</para></entry>
27080+
</row>
27081+
2706427082
<row>
2706527083
<entry role="func_table_entry"><para role="func_signature">
2706627084
<indexterm>

‎doc/src/sgml/ref/alter_collation.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -151,7 +151,8 @@ HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg
151151
</para>
152152
</note>
153153
<para>
154-
Currently, there is no version tracking for the database default collation.
154+
For the database default collation, there is an analogous command
155+
<literal>ALTER DATABASE ... REFRESH COLLATION VERSION</literal>.
155156
</para>
156157

157158
<para>

‎doc/src/sgml/ref/alter_database.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,8 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> OWNER TO { <rep
3535

3636
ALTER DATABASE <replaceable class="parameter">name</replaceable> SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
3737

38+
ALTER DATABASE <replaceable class="parameter">name</replaceable> REFRESH COLLATION VERSION
39+
3840
ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
3941
ALTER DATABASE <replaceable class="parameter">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
4042
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
171173
</listitem>
172174
</varlistentry>
173175

176+
<varlistentry>
177+
<term><literal>REFRESH COLLATION VERSION</literal></term>
178+
<listitem>
179+
<para>
180+
Update the database collation version. See <xref
181+
linkend="sql-altercollation-notes"/> for background.
182+
</para>
183+
</listitem>
184+
</varlistentry>
185+
174186
<varlistentry>
175187
<term><replaceable>configuration_parameter</replaceable></term>
176188
<term><replaceable>value</replaceable></term>

‎doc/src/sgml/ref/create_database.sgml

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
2828
[ LOCALE [=] <replaceable class="parameter">locale</replaceable> ]
2929
[ LC_COLLATE [=] <replaceable class="parameter">lc_collate</replaceable> ]
3030
[ LC_CTYPE [=] <replaceable class="parameter">lc_ctype</replaceable> ]
31+
[ COLLATION_VERSION = <replaceable>collation_version</replaceable> ]
3132
[ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
3233
[ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
3334
[ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ]
@@ -158,6 +159,26 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
158159
</para>
159160
</listitem>
160161
</varlistentry>
162+
163+
<varlistentry>
164+
<term><replaceable>collation_version</replaceable></term>
165+
166+
<listitem>
167+
<para>
168+
Specifies the collation version string to store with the database.
169+
Normally, this should be omitted, which will cause the version to be
170+
computed from the actual version of the database collation as provided
171+
by the operating system. This option is intended to be used by
172+
<command>pg_upgrade</command> for copying the version from an existing
173+
installation.
174+
</para>
175+
176+
<para>
177+
See also <xref linkend="sql-alterdatabase"/> for how to handle
178+
database collation version mismatches.
179+
</para>
180+
</listitem>
181+
</varlistentry>
161182
<varlistentry>
162183
<term><replaceable class="parameter">tablespace_name</replaceable></term>
163184
<listitem>

0 commit comments

Comments
 (0)