This patch 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. And of course ICU on
database-level is being worked on at the moment as well.
This patch is pretty much complete AFAICT. One bonus thing would be to
add a query to the ALTER DATABASE ref page similar to the one on the
ALTER COLLATION ref page that identifies the objects that are affected
by outdated collations. The database version of that might just show
all collation-using objects or something like that. Suggestions welcome.
Also, one curious behavior is that if you get to a situation where the
collation version is mismatched, every time an autovacuum worker
launches you get the collation version mismatch warning in the log.
Maybe that's actually correct, but maybe we want to dial it down a bit
for non-interactive sessions.From c413bd65fb3dbbfea6c724970709501f73946513 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 1 Feb 2022 16:09:30 +0100
Subject: [PATCH] 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.
---
doc/src/sgml/catalogs.sgml | 11 ++
doc/src/sgml/func.sgml | 18 ++++
doc/src/sgml/ref/alter_collation.sgml | 3 +-
doc/src/sgml/ref/alter_database.sgml | 12 +++
src/backend/commands/dbcommands.c | 146 ++++++++++++++++++++++++--
src/backend/parser/gram.y | 6 ++
src/backend/tcop/utility.c | 14 +--
src/backend/utils/init/postinit.c | 33 ++++++
src/bin/initdb/initdb.c | 12 +++
src/bin/pg_dump/pg_dump.c | 20 ++++
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_database.h | 3 +
src/include/catalog/pg_proc.dat | 5 +
src/include/commands/dbcommands.h | 1 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 6 ++
16 files changed, 279 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 7d5b0b1656..8d6f33f88c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3043,6 +3043,17 @@ <title><structname>pg_database</structname>
Columns</title>
</para></entry>
</row>
+ <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>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8754f2f89b..49644666bc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27061,6 +27061,24 @@ <title>Collation Management Functions</title>
</para></entry>
</row>
+ <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>
diff --git a/doc/src/sgml/ref/alter_collation.sgml
b/doc/src/sgml/ref/alter_collation.sgml
index 892c466565..a8c831d728 100644
--- a/doc/src/sgml/ref/alter_collation.sgml
+++ b/doc/src/sgml/ref/alter_collation.sgml
@@ -151,7 +151,8 @@ <title>Notes</title>
</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 81e37536a3..89ed261b4c 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> 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 @@ <title>Parameters</title>
</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/src/backend/commands/dbcommands.c
b/src/backend/commands/dbcommands.c
index 5a6c9beaa4..75d45c8145 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -36,6 +36,7 @@
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_subscription.h"
@@ -85,7 +86,8 @@ static bool get_db_info(const char *name, LOCKMODE lockmode,
Oid *dbIdP, Oid *ownerIdP,
int *encodingP, bool
*dbIsTemplateP, bool *dbAllowConnP,
TransactionId *dbFrozenXidP,
MultiXactId *dbMinMultiP,
- Oid *dbTablespace, char
**dbCollate, char **dbCtype);
+ Oid *dbTablespace, char
**dbCollate, char **dbCtype,
+ char **dbCollversion);
static bool have_createdb_privilege(void);
static void remove_dbtablespaces(Oid db_id);
static bool check_db_file_conflict(Oid db_id);
@@ -105,6 +107,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
int src_encoding = -1;
char *src_collate = NULL;
char *src_ctype = NULL;
+ char *src_collversion = NULL;
bool src_istemplate;
bool src_allowconn;
TransactionId src_frozenxid = InvalidTransactionId;
@@ -128,6 +131,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
DefElem *distemplate = NULL;
DefElem *dallowconnections = NULL;
DefElem *dconnlimit = NULL;
+ DefElem *dcollversion = NULL;
char *dbname = stmt->dbname;
char *dbowner = NULL;
const char *dbtemplate = NULL;
@@ -138,6 +142,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
bool dbistemplate = false;
bool dballowconnections = true;
int dbconnlimit = -1;
+ char *dbcollversion = NULL;
int notherbackends;
int npreparedxacts;
createdb_failure_params fparms;
@@ -207,6 +212,12 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
errorConflictingDefElem(defel, pstate);
dconnlimit = defel;
}
+ else if (strcmp(defel->defname, "collation_version") == 0)
+ {
+ if (dcollversion)
+ errorConflictingDefElem(defel, pstate);
+ dcollversion = defel;
+ }
else if (strcmp(defel->defname, "location") == 0)
{
ereport(WARNING,
@@ -305,6 +316,8 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid connection limit: %d",
dbconnlimit)));
}
+ if (dcollversion)
+ dbcollversion = defGetString(dcollversion);
/* obtain OID of proposed owner */
if (dbowner)
@@ -342,7 +355,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
&src_dboid, &src_owner, &src_encoding,
&src_istemplate, &src_allowconn,
&src_frozenxid, &src_minmxid,
&src_deftablespace,
- &src_collate, &src_ctype))
+ &src_collate, &src_ctype,
&src_collversion))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("template database \"%s\" does not
exist",
@@ -368,6 +381,8 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
dbcollate = src_collate;
if (dbctype == NULL)
dbctype = src_ctype;
+ if (dbcollversion == NULL)
+ dbcollversion = src_collversion;
/* Some encodings are client only */
if (!PG_VALID_BE_ENCODING(encoding))
@@ -424,6 +439,14 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
errhint("Use the same LC_CTYPE as in
the template database, or use template0 as template.")));
}
+ /*
+ * Normally, we copy the collation version from the template database.
+ * This last resort only applies if the template database does not have
a
+ * collation version, which is normally only the case for template0.
+ */
+ if (!dbcollversion)
+ dbcollversion = get_collation_actual_version(COLLPROVIDER_LIBC,
dbcollate);
+
/* Resolve default tablespace for new database */
if (dtablespacename && dtablespacename->arg)
{
@@ -578,6 +601,10 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
new_record[Anum_pg_database_datfrozenxid - 1] =
TransactionIdGetDatum(src_frozenxid);
new_record[Anum_pg_database_datminmxid - 1] =
TransactionIdGetDatum(src_minmxid);
new_record[Anum_pg_database_dattablespace - 1] =
ObjectIdGetDatum(dst_deftablespace);
+ if (dbcollversion)
+ new_record[Anum_pg_database_datcollversion - 1] =
CStringGetTextDatum(dbcollversion);
+ else
+ new_record_nulls[Anum_pg_database_datcollversion - 1] = true;
/*
* We deliberately set datacl to default (NULL), rather than copying it
@@ -844,7 +871,7 @@ dropdb(const char *dbname, bool missing_ok, bool force)
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
- &db_istemplate, NULL, NULL, NULL,
NULL, NULL, NULL))
+ &db_istemplate, NULL, NULL, NULL,
NULL, NULL, NULL, NULL))
{
if (!missing_ok)
{
@@ -1043,7 +1070,7 @@ RenameDatabase(const char *oldname, const char *newname)
rel = table_open(DatabaseRelationId, RowExclusiveLock);
if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL,
- NULL, NULL, NULL, NULL, NULL, NULL,
NULL))
+ NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" does not exist",
oldname)));
@@ -1156,7 +1183,7 @@ movedb(const char *dbname, const char *tblspcname)
pgdbrel = table_open(DatabaseRelationId, RowExclusiveLock);
if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
- NULL, NULL, NULL, NULL,
&src_tblspcoid, NULL, NULL))
+ NULL, NULL, NULL, NULL,
&src_tblspcoid, NULL, NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("database \"%s\" does not exist",
dbname)));
@@ -1643,6 +1670,76 @@ AlterDatabase(ParseState *pstate, AlterDatabaseStmt
*stmt, bool isTopLevel)
}
+/*
+ * ALTER DATABASE name REFRESH COLLATION VERSION
+ */
+Oid
+AlterDatabaseRefreshColl(AlterDatabaseRefreshCollStmt *stmt)
+{
+ Relation rel;
+ Oid dboid;
+ HeapTuple tup;
+ Datum datum;
+ bool isnull;
+ char *oldversion;
+ char *newversion;
+
+ rel = table_open(DatabaseRelationId, RowExclusiveLock);
+ dboid = get_database_oid(stmt->dbname, false);
+
+ if (!pg_database_ownercheck(dboid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_DATABASE,
+ stmt->dbname);
+
+ tup = SearchSysCacheCopy1(DATABASEOID, ObjectIdGetDatum(dboid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for database %u", dboid);
+
+ datum = SysCacheGetAttr(DATABASEOID, tup,
Anum_pg_database_datcollversion, &isnull);
+ oldversion = isnull ? NULL : TextDatumGetCString(datum);
+
+ datum = SysCacheGetAttr(DATABASEOID, tup, Anum_pg_database_datcollate,
&isnull);
+ Assert(!isnull);
+ newversion = get_collation_actual_version(COLLPROVIDER_LIBC,
TextDatumGetCString(datum));
+
+ /* cannot change from NULL to non-NULL or vice versa */
+ if ((!oldversion && newversion) || (oldversion && !newversion))
+ elog(ERROR, "invalid collation version change");
+ else if (oldversion && newversion && strcmp(newversion, oldversion) !=
0)
+ {
+ bool nulls[Natts_pg_database];
+ bool replaces[Natts_pg_database];
+ Datum values[Natts_pg_database];
+
+ ereport(NOTICE,
+ (errmsg("changing version from %s to %s",
+ oldversion, newversion)));
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+ memset(replaces, false, sizeof(replaces));
+
+ values[Anum_pg_database_datcollversion - 1] =
CStringGetTextDatum(newversion);
+ replaces[Anum_pg_database_datcollversion - 1] = true;
+
+ tup = heap_modify_tuple(tup, RelationGetDescr(rel),
+ values, nulls,
replaces);
+ }
+ else
+ ereport(NOTICE,
+ (errmsg("version has not changed")));
+
+ CatalogTupleUpdate(rel, &tup->t_self, tup);
+
+ InvokeObjectPostAlterHook(DatabaseRelationId, dboid, 0);
+
+ heap_freetuple(tup);
+ table_close(rel, NoLock);
+
+ return dboid;
+}
+
+
/*
* ALTER DATABASE name SET ...
*/
@@ -1785,6 +1882,34 @@ AlterDatabaseOwner(const char *dbname, Oid newOwnerId)
}
+Datum
+pg_database_collation_actual_version(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ HeapTuple tp;
+ Datum datum;
+ bool isnull;
+ char *version;
+
+ tp = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
+ if (!HeapTupleIsValid(tp))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("database with OID %u does not exist",
dbid)));
+
+ datum = SysCacheGetAttr(DATABASEOID, tp, Anum_pg_database_datcollate,
&isnull);
+ Assert(!isnull);
+ version = get_collation_actual_version(COLLPROVIDER_LIBC,
TextDatumGetCString(datum));
+
+ ReleaseSysCache(tp);
+
+ if (version)
+ PG_RETURN_TEXT_P(cstring_to_text(version));
+ else
+ PG_RETURN_NULL();
+}
+
+
/*
* Helper functions
*/
@@ -1800,7 +1925,8 @@ get_db_info(const char *name, LOCKMODE lockmode,
Oid *dbIdP, Oid *ownerIdP,
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
TransactionId *dbFrozenXidP, MultiXactId *dbMinMultiP,
- Oid *dbTablespace, char **dbCollate, char **dbCtype)
+ Oid *dbTablespace, char **dbCollate, char **dbCtype,
+ char **dbCollversion)
{
bool result = false;
Relation relation;
@@ -1905,6 +2031,14 @@ get_db_info(const char *name, LOCKMODE lockmode,
Assert(!isnull);
*dbCtype = TextDatumGetCString(datum);
}
+ if (dbCollversion)
+ {
+ datum = SysCacheGetAttr(DATABASEOID,
tuple, Anum_pg_database_datcollversion, &isnull);
+ if (isnull)
+ *dbCollversion = NULL;
+ else
+ *dbCollversion =
TextDatumGetCString(datum);
+ }
ReleaseSysCache(tuple);
result = true;
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b5966712ce..041771f8b7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10454,6 +10454,12 @@ AlterDatabaseStmt:
(Node *)makeString($6), @6));
$$ = (Node *)n;
}
+ | ALTER DATABASE name REFRESH COLLATION VERSION_P
+ {
+ AlterDatabaseRefreshCollStmt *n =
makeNode(AlterDatabaseRefreshCollStmt);
+ n->dbname = $3;
+ $$ = (Node *)n;
+ }
;
AlterDatabaseSetStmt:
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 83e4e37c78..3780c6e812 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -136,6 +136,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
switch (nodeTag(parsetree))
{
case T_AlterCollationStmt:
+ case T_AlterDatabaseRefreshCollStmt:
case T_AlterDatabaseSetStmt:
case T_AlterDatabaseStmt:
case T_AlterDefaultPrivilegesStmt:
@@ -779,6 +780,11 @@ standard_ProcessUtility(PlannedStmt *pstmt,
AlterDatabase(pstate, (AlterDatabaseStmt *) parsetree,
isTopLevel);
break;
+ case T_AlterDatabaseRefreshCollStmt:
+ /* no event triggers for global objects */
+ AlterDatabaseRefreshColl((AlterDatabaseRefreshCollStmt
*) parsetree);
+ break;
+
case T_AlterDatabaseSetStmt:
/* no event triggers for global objects */
AlterDatabaseSet((AlterDatabaseSetStmt *) parsetree);
@@ -2801,9 +2807,7 @@ CreateCommandTag(Node *parsetree)
break;
case T_AlterDatabaseStmt:
- tag = CMDTAG_ALTER_DATABASE;
- break;
-
+ case T_AlterDatabaseRefreshCollStmt:
case T_AlterDatabaseSetStmt:
tag = CMDTAG_ALTER_DATABASE;
break;
@@ -3444,9 +3448,7 @@ GetCommandLogLevel(Node *parsetree)
break;
case T_AlterDatabaseStmt:
- lev = LOGSTMT_DDL;
- break;
-
+ case T_AlterDatabaseRefreshCollStmt:
case T_AlterDatabaseSetStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/backend/utils/init/postinit.c
b/src/backend/utils/init/postinit.c
index 5b9ed2f6f5..228bb63f97 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -31,6 +31,7 @@
#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
@@ -414,6 +415,38 @@ CheckMyDatabase(const char *name, bool am_superuser, bool
override_allow_connect
" which is not recognized by
setlocale().", ctype),
errhint("Recreate the database with another
locale or install the missing locale.")));
+ /*
+ * Check collation version. See similar code in
+ * pg_newlocale_from_collation().
+ */
+ datum = SysCacheGetAttr(DATABASEOID, tup,
Anum_pg_database_datcollversion,
+ &isnull);
+ if (!isnull)
+ {
+ char *actual_versionstr;
+ char *collversionstr;
+
+ collversionstr = TextDatumGetCString(datum);
+
+ actual_versionstr =
get_collation_actual_version(COLLPROVIDER_LIBC, collate);
+ if (!actual_versionstr)
+ ereport(ERROR,
+ (errmsg("database \"%s\" has no actual
collation version, but a version was specified",
+ name)));
+
+ if (strcmp(actual_versionstr, collversionstr) != 0)
+ ereport(WARNING,
+ (errmsg("database \"%s\" has a
collation version mismatch",
+ name),
+ errdetail("The database was created
using collation version %s, "
+ "but the operating
system provides version %s.",
+ collversionstr,
actual_versionstr),
+ errhint("Rebuild all objects affected
by collation in this database and run "
+ "ALTER DATABASE %s
REFRESH COLLATION VERSION, "
+ "or build PostgreSQL
with the right library version.",
+
quote_identifier(name))));
+ }
+
/* Make the locale settings visible as GUC variables, too */
SetConfigOption("lc_collate", collate, PGC_INTERNAL, PGC_S_OVERRIDE);
SetConfigOption("lc_ctype", ctype, PGC_INTERNAL, PGC_S_OVERRIDE);
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index d78e8e67b8..6e70d1b724 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1857,6 +1857,18 @@ make_template0(FILE *cmdfd)
"CREATE DATABASE template0 IS_TEMPLATE = true ALLOW_CONNECTIONS
= false OID = "
CppAsString2(Template0ObjectId) ";\n\n",
+ /*
+ * template0 shouldn't have any collation-dependent objects, so
unset
+ * the collation version. This avoids warnings when making a
new
+ * database from it.
+ */
+ "UPDATE pg_database SET datcollversion = NULL WHERE datname =
'template0';\n\n",
+
+ /*
+ * While we are here, do set the collation version on template1.
+ */
+ "UPDATE pg_database SET datcollversion =
pg_database_collation_actual_version(oid) WHERE datname = 'template1';\n\n",
+
/*
* Explicitly revoke public create-schema and create-temp-table
* privileges in template1 and template0; else the latter would
be on
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e3ddf19959..e75dcee62c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2761,6 +2761,7 @@ dumpDatabase(Archive *fout)
i_acldefault,
i_datistemplate,
i_datconnlimit,
+ i_datcollversion,
i_tablespace;
CatalogId dbCatId;
DumpId dbDumpId;
@@ -2792,6 +2793,10 @@ dumpDatabase(Archive *fout)
appendPQExpBuffer(dbQry, "datminmxid, ");
else
appendPQExpBuffer(dbQry, "0 AS datminmxid, ");
+ if (fout->remoteVersion >= 150000)
+ appendPQExpBuffer(dbQry, "datcollversion, ");
+ else
+ appendPQExpBuffer(dbQry, "NULL AS datcollversion, ");
appendPQExpBuffer(dbQry,
"(SELECT spcname FROM pg_tablespace t
WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid,
'pg_database') AS description "
@@ -2813,6 +2818,7 @@ dumpDatabase(Archive *fout)
i_acldefault = PQfnumber(res, "acldefault");
i_datistemplate = PQfnumber(res, "datistemplate");
i_datconnlimit = PQfnumber(res, "datconnlimit");
+ i_datcollversion = PQfnumber(res, "datcollversion");
i_tablespace = PQfnumber(res, "tablespace");
dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
@@ -2871,6 +2877,20 @@ dumpDatabase(Archive *fout)
appendStringLiteralAH(creaQry, ctype, fout);
}
}
+ /*
+ * For binary upgrade, carry over the collation version. For normal
+ * dump/restore, omit the version, so that it is computed upon restore.
+ */
+ if (dopt->binary_upgrade)
+ {
+ if (!PQgetisnull(res, 0, i_datcollversion))
+ {
+ appendPQExpBufferStr(creaQry, " COLLATION_VERSION = ");
+ appendStringLiteralAH(creaQry,
+
PQgetvalue(res, 0, i_datcollversion),
+ fout);
+ }
+ }
/*
* Note: looking at dopt->outputNoTablespaces here is completely the
wrong
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b2ec50b4f2..cc9319d434 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1839,7 +1839,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER DATABASE <name> */
else if (Matches("ALTER", "DATABASE", MatchAny))
- COMPLETE_WITH("RESET", "SET", "OWNER TO", "RENAME TO",
+ COMPLETE_WITH("RESET", "SET", "OWNER TO", "REFRESH COLLATION
VERSION", "RENAME TO",
"IS_TEMPLATE", "ALLOW_CONNECTIONS",
"CONNECTION LIMIT");
diff --git a/src/include/catalog/pg_database.h
b/src/include/catalog/pg_database.h
index 90b43a4ecc..76adbd4aad 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -65,6 +65,9 @@ CATALOG(pg_database,1262,DatabaseRelationId)
BKI_SHARED_RELATION BKI_ROWTYPE_OID
/* LC_CTYPE setting */
text datctype BKI_FORCE_NOT_NULL;
+ /* provider-dependent version of collation data */
+ text datcollversion BKI_DEFAULT(_null_);
+
/* access permissions */
aclitem datacl[1];
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7024dbe10a..2f45c764e2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11615,6 +11615,11 @@
proname => 'pg_collation_actual_version', procost => '100',
provolatile => 'v', prorettype => 'text', proargtypes => 'oid',
prosrc => 'pg_collation_actual_version' },
+{ oid => '9167',
+ descr => 'get actual version of database collation from operating system',
+ proname => 'pg_database_collation_actual_version', procost => '100',
+ provolatile => 'v', prorettype => 'text', proargtypes => 'oid',
+ prosrc => 'pg_database_collation_actual_version' },
# system management/monitoring related functions
{ oid => '3353', descr => 'list files in the log directory',
diff --git a/src/include/commands/dbcommands.h
b/src/include/commands/dbcommands.h
index b1e8b5eb96..7faf6bf41c 100644
--- a/src/include/commands/dbcommands.h
+++ b/src/include/commands/dbcommands.h
@@ -24,6 +24,7 @@ extern void dropdb(const char *dbname, bool missing_ok, bool
force);
extern void DropDatabase(ParseState *pstate, DropdbStmt *stmt);
extern ObjectAddress RenameDatabase(const char *oldname, const char *newname);
extern Oid AlterDatabase(ParseState *pstate, AlterDatabaseStmt *stmt, bool
isTopLevel);
+extern Oid AlterDatabaseRefreshColl(AlterDatabaseRefreshCollStmt *stmt);
extern Oid AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
extern ObjectAddress AlterDatabaseOwner(const char *dbname, Oid newOwnerId);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index da35f2c272..5d075f0c34 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -370,6 +370,7 @@ typedef enum NodeTag
T_CheckPointStmt,
T_CreateSchemaStmt,
T_AlterDatabaseStmt,
+ T_AlterDatabaseRefreshCollStmt,
T_AlterDatabaseSetStmt,
T_AlterRoleSetStmt,
T_CreateConversionStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3e9bdc781f..d901cf2a35 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3306,6 +3306,12 @@ typedef struct AlterDatabaseStmt
List *options; /* List of DefElem nodes */
} AlterDatabaseStmt;
+typedef struct AlterDatabaseRefreshCollStmt
+{
+ NodeTag type;
+ char *dbname;
+} AlterDatabaseRefreshCollStmt;
+
typedef struct AlterDatabaseSetStmt
{
NodeTag type;
--
2.35.1
