As mentioned in [0], pg_upgrade currently does not preserve the version
of collation objects created by initdb. Here is an attempt to fix that.
The way I deal with this here is by having the binary-upgrade mode in
pg_dump delete all the collations created by initdb and then dump out
CREATE COLLATION commands with version information normally.
I had originally imagined doing some kind of ALTER COLLATION (or perhaps
a direct UPDATE pg_collation) to update the version information, but
that doesn't really work because we don't know whether the collation
object with a given name in the new cluster is the same as the one in
the old cluster. So it seems more robust to just delete all existing
collations and create them from scratch.
Thoughts?
[0]:
https://www.postgresql.org/message-id/ca+hukgkde98dfwkjos7e4z+oamzc-1szfpl3v3ppgi1unvq...@mail.gmail.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From f4e804b3c1ccc7c36fe8d253ae74955ab1855448 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 28 Oct 2019 13:38:56 +0100
Subject: [PATCH v1] Preserve versions of initdb-created collations in
pg_upgrade
---
src/bin/pg_dump/pg_dump.c | 31 +++++++++++++++++++++++++++++--
1 file changed, 29 insertions(+), 2 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bf69adc2f4..f54ea2e73e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1649,6 +1649,28 @@ selectDumpableCast(CastInfo *cast, Archive *fout)
DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
}
+/*
+ * selectDumpableCollation: policy-setting subroutine
+ * Mark a collation as to be dumped or not
+ */
+static void
+selectDumpableCollation(CollInfo *collinfo, Archive *fout)
+{
+ DumpOptions *dopt = fout->dopt;
+
+ if (checkExtensionMembership(&collinfo->dobj, fout))
+ return; /* extension membership
overrides all else */
+
+ collinfo->dobj.dump = collinfo->dobj.namespace->dobj.dump_contains;
+
+ /*
+ * Collations in pg_catalog created by initdb must be dumped in
+ * binary-upgrade mode to preserve the collation version.
+ */
+ if (dopt->binary_upgrade && collinfo->dobj.catId.oid >=
FirstBootstrapObjectId)
+ collinfo->dobj.dump = DUMP_COMPONENT_ALL;
+}
+
/*
* selectDumpableProcLang: policy-setting subroutine
* Mark a procedural language as to be dumped or not
@@ -2980,6 +3002,10 @@ dumpDatabase(Archive *fout)
frozenxid, minmxid);
appendStringLiteralAH(creaQry, datname, fout);
appendPQExpBufferStr(creaQry, ";\n");
+
+ appendPQExpBufferStr(creaQry, "\n-- Remove collations created
by initdb.\n");
+ appendPQExpBuffer(creaQry, "DELETE FROM pg_catalog.pg_collation
WHERE oid > %u;\n",
+ FirstBootstrapObjectId);
}
if (creaQry->len > 0)
@@ -5107,7 +5133,8 @@ getCollations(Archive *fout, int *numCollations)
appendPQExpBuffer(query, "SELECT tableoid, oid, collname, "
"collnamespace, "
"(%s collowner) AS rolname "
- "FROM pg_collation",
+ "FROM pg_collation "
+ "WHERE collencoding = (SELECT
encoding FROM pg_database WHERE datname = current_database()) OR collencoding =
-1",
username_subquery);
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -5136,7 +5163,7 @@ getCollations(Archive *fout, int *numCollations)
collinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
/* Decide whether we want to dump it */
- selectDumpableObject(&(collinfo[i].dobj), fout);
+ selectDumpableCollation(&collinfo[i], fout);
/* Collations do not currently have ACLs. */
collinfo[i].dobj.dump &= ~DUMP_COMPONENT_ACL;
base-commit: 61ecea45e50bcd3b87d4e905719e63e41d6321ce
--
2.23.0
