On Tue, Feb 01, 2022 at 04:20:14PM +0100, Peter Eisentraut wrote:
> This patch adds to database objects the same version tracking that collation
> objects have.
This version conflicts with 87669de72c2 (Some cleanup for change of collate and
ctype fields to type text), so I'm attaching a simple rebase of your patch to
make the cfbot happy, no other changes.
> 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.
Agreed. Here's a review of the patch:
- there should be a mention to the need for a catversion bump in the message
comment
- there is no test
- it's missing some updates in create_database.sgml, and psql tab completion
for CREATE DATABASE with the new collation_version defelem.
- that's not really something new with this patch, but should we output the
collation version info or mismatch info in \l / \dO?
+ 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?
+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?
I guess it's not worth bothering as it can only lead to spurious messages for
connection done concurrently, but there should be a comment to clarify it.
Also, it means that someone can drop the database concurrently. So it's
should be a "database does not exist" rather than a cache lookup failed error
message.
+ /*
+ * 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.
+ /*
+ * 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
DETAIL: The database was created using collation version meh, but the
operating system provides version 2.34.
HINT: Rebuild all objects affected by collation in this database and run ALTER
DATABASE test2 REFRESH COLLATION VERSION, or build PostgreSQL with the right
library version.
You are now connected to database "test2" as user "rjuju".
The rest of the patch looks good to me. There's notably pg_dump and pg_upgrade
support so it indeed looks complete.
> 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.
That would be nice, but that's something quite hard to do and the resulting
query would be somewhat unreadable.
Also, you need to look at custom data types, expression and quals at least, so
I'm not even sure that you can actually do it in pure SQL without additional
infrastructure.
> 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.
I'm +0.5 to keep it that way. Index corruption is a real danger, so if you
have enough autovacuum worker launched to have a real problem with that, you
clearly should take care of the problem even faster.
>From 718942496eb0fa19ab092e3c74af42149f86f756 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 1 Feb 2022 16:09:30 +0100
Subject: [PATCH v2] 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 879d2dbce0..5a1627a394 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3043,6 +3043,17 @@ SCRAM-SHA-256$<replaceable><iteration
count></replaceable>:<replaceable>&l
</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 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
</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 @@ 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 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> 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/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..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 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.33.1
