On 07.02.22 11:29, Julien Rouhaud wrote:
- there should be a mention to the need for a catversion bump in the message
comment
done
- there is no test
Suggestions where to put it? We don't really have tests for the
collation-level versioning either, do we?
- it's missing some updates in create_database.sgml, and psql tab completion
for CREATE DATABASE with the new collation_version defelem.
Added to create_database.sgml, but not to psql. We don't have
completion for the collation option either, since it's only meant to be
used by pg_upgrade, not interactively.
- that's not really something new with this patch, but should we output the
collation version info or mismatch info in \l / \dO?
It's a possibility. Perhaps there is a question of performance if we
show it in \l and people have tons of databases and they have to make a
locale call for each one. As you say, it's more an independent feature,
but it's worth looking into.
+ if (!actual_versionstr)
+ ereport(ERROR,
+ (errmsg("database \"%s\" has no actual collation version, but a
version was specified",
+ name)));-
this means you can't connect on such a database anymore. The level is probably
ok for collation version check, but for db isn't that too much?
Right, changed to warning.
+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);
Is that ok to not obtain a lock on the database when refreshing the collation?
That code takes a RowExclusiveLock on pg_database. Did you have
something else in mind?
+ /*
+ * Check collation version. See similar code in
+ * pg_newlocale_from_collation().
+ */
+ datum = SysCacheGetAttr(DATABASEOID, tup, Anum_pg_database_datcollversion,
+ &isnull);
+ if (!isnull)
+ {
This (and pg_newlocale_from_collation()) reports a problem if a recorded
collation version is found but there's no reported collation version.
Shouldn't it also complain if it's the opposite? It's otherwise a backdoor to
make sure there won't be any check about the version anymore, and while it can
probably happen if you mess with the catalogs it still doesn't look great.
get_collation_actual_version() always returns either null or not null
for a given installation. So the situation that the stored version is
null and the actual version is not null can only happen as part of a
software upgrade. In that case, all uses of collations after an upgrade
would immediately start complaining about missing versions, which seems
like a bad experience. Users can explicitly opt in to version tracking
by running REFRESH VERSION once.
+ /*
+ * 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",
I'm not opposed, but shouldn't there indeed be a warning in case of discrepancy
in the source database (whether template or not)?
# update pg_database set datcollversion = 'meh' where datname in ('postgres',
'template1');
UPDATE 2
# create database test1 template postgres;
CREATE DATABASE
# create database test2 template template1;
CREATE DATABASE
# \c test2
WARNING: database "test2" has a collation version mismatch
I don't understand what the complaint is here. It seems to work ok?
From c50f4561932f5ff23715a0fb6e3e9554ce395f16 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 7 Feb 2022 16:32:19 +0100
Subject: [PATCH v3] 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.
Discussion:
https://www.postgresql.org/message-id/flat/f0ff3190-29a3-5b39-a179-fa32eee57db6%40enterprisedb.com
XXX catversion bump
---
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 +++
doc/src/sgml/ref/create_database.sgml | 21 ++++
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 ++
17 files changed, 300 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 879d2dbce0..5a1627a394 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/doc/src/sgml/ref/create_database.sgml
b/doc/src/sgml/ref/create_database.sgml
index f22e28dc81..f70d0c75b4 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -28,6 +28,7 @@
[ 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 @@ <title>Parameters</title>
</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>
diff --git a/src/backend/commands/dbcommands.c
b/src/backend/commands/dbcommands.c
index e673138cbd..78010f2198 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_dattablespace - 1] =
ObjectIdGetDatum(dst_deftablespace);
new_record[Anum_pg_database_datcollate - 1] =
CStringGetTextDatum(dbcollate);
new_record[Anum_pg_database_datctype - 1] =
CStringGetTextDatum(dbctype);
+ 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 c4f3242506..92f93cfc72 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10465,6 +10465,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..f79e89a915 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(WARNING,
+ (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 3499c0a4d5..7df576961e 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 d1e421bc0f..d8ea142856 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 37fcc4c9b5..34218b718c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3308,6 +3308,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
