On Tue, Sep 25, 2012 at 09:10:33AM -0400, Bruce Momjian wrote:
> > lc_collate cluster values do not match: old "zh_CN.utf8", new "zh_CN.UTF-8"
> > Failure, exiting
> >
> > zh_CN.utf8 is provided by the installer and zh_CN.UTF-8 is my system
> > default.
>
> OK, this tells us that the canonicalization code used in initdb is not
> going to help us in pg_upgrade, at least not on your system, and not on
> mine.
>
> I think we should apply the patch that fixes the TOAST problem with
> information_schema, and the patch that outputs the old/new values for
> easier debugging. Other than that, I don't know what else we can do
> except to ignore dashes when comparing locale names, which I am told is
> unacceptable.
Based on this great bug report and submitter leg-work, I have applied
three patches to pg_upgrade in head and 9.2, all attached:
* try to get the canonical locale names, and report old/new values on mismatch
* update query to skip toast tables for system objects
* improve error reporting when the object counts don't match
None of these bugs caused pg_upgrade to produce an incorrect upgraded
cluster, so I am not going to panic and try to force them into 9.1,
which probably isn't being used by many people anymore anyway.
I think this closes this report.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
new file mode 100644
index 74b13e7..9d08f41
*** a/contrib/pg_upgrade/info.c
--- b/contrib/pg_upgrade/info.c
*************** get_rel_infos(ClusterInfo *cluster, DbIn
*** 269,302 ****
*/
snprintf(query, sizeof(query),
! "SELECT c.oid, n.nspname, c.relname, "
! " c.relfilenode, c.reltablespace, %s "
"FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
" ON c.relnamespace = n.oid "
! " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
! " ON c.reltablespace = t.oid "
! "WHERE relkind IN ('r','t', 'i'%s) AND "
/* exclude possible orphaned temp tables */
" ((n.nspname !~ '^pg_temp_' AND "
" n.nspname !~ '^pg_toast_temp_' AND "
! " n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') AND "
" c.oid >= %u) "
" OR (n.nspname = 'pg_catalog' AND "
! " relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) )) "
! /* we preserve pg_class.oid so we sort by it to match old/new */
! "ORDER BY 1;",
! /* 9.2 removed the spclocation column */
! (GET_MAJOR_VERSION(cluster->major_version) <= 901) ?
! "t.spclocation" : "pg_catalog.pg_tablespace_location(t.oid) AS spclocation",
/* see the comment at the top of old_8_3_create_sequence_script() */
(GET_MAJOR_VERSION(old_cluster.major_version) <= 803) ?
"" : ", 'S'",
- /* this oid allows us to skip system toast tables */
FirstNormalObjectId,
/* does pg_largeobject_metadata need to be migrated? */
(GET_MAJOR_VERSION(old_cluster.major_version) <= 804) ?
"" : ", 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index'");
res = executeQueryOrDie(conn, "%s", query);
ntups = PQntuples(res);
--- 269,327 ----
*/
snprintf(query, sizeof(query),
! "CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid "
"FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
" ON c.relnamespace = n.oid "
! "WHERE relkind IN ('r', 'i'%s) AND "
/* exclude possible orphaned temp tables */
" ((n.nspname !~ '^pg_temp_' AND "
" n.nspname !~ '^pg_toast_temp_' AND "
! /* skip pg_toast because toast index have relkind == 'i', not 't' */
! " n.nspname NOT IN ('pg_catalog', 'information_schema', "
! " 'binary_upgrade', 'pg_toast') AND "
" c.oid >= %u) "
" OR (n.nspname = 'pg_catalog' AND "
! " relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) ));",
/* see the comment at the top of old_8_3_create_sequence_script() */
(GET_MAJOR_VERSION(old_cluster.major_version) <= 803) ?
"" : ", 'S'",
FirstNormalObjectId,
/* does pg_largeobject_metadata need to be migrated? */
(GET_MAJOR_VERSION(old_cluster.major_version) <= 804) ?
"" : ", 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index'");
+ PQclear(executeQueryOrDie(conn, "%s", query));
+
+ /*
+ * Get TOAST tables and indexes; we have to gather the TOAST tables in
+ * later steps because we can't schema-qualify TOAST tables.
+ */
+ PQclear(executeQueryOrDie(conn,
+ "INSERT INTO info_rels "
+ "SELECT reltoastrelid "
+ "FROM info_rels i JOIN pg_catalog.pg_class c "
+ " ON i.reloid = c.oid"));
+ PQclear(executeQueryOrDie(conn,
+ "INSERT INTO info_rels "
+ "SELECT reltoastidxid "
+ "FROM info_rels i JOIN pg_catalog.pg_class c "
+ " ON i.reloid = c.oid"));
+
+ snprintf(query, sizeof(query),
+ "SELECT c.oid, n.nspname, c.relname, "
+ " c.relfilenode, c.reltablespace, %s "
+ "FROM info_rels i JOIN pg_catalog.pg_class c "
+ " ON i.reloid = c.oid "
+ " JOIN pg_catalog.pg_namespace n "
+ " ON c.relnamespace = n.oid "
+ " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
+ " ON c.reltablespace = t.oid "
+ /* we preserve pg_class.oid so we sort by it to match old/new */
+ "ORDER BY 1;",
+ /* 9.2 removed the spclocation column */
+ (GET_MAJOR_VERSION(cluster->major_version) <= 901) ?
+ "t.spclocation" : "pg_catalog.pg_tablespace_location(t.oid) AS spclocation");
+
res = executeQueryOrDie(conn, "%s", query);
ntups = PQntuples(res);
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index bed10f8..e4fec34
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** static void check_for_prepared_transacti
*** 21,26 ****
--- 21,27 ----
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
static void get_bin_version(ClusterInfo *cluster);
+ static char *get_canonical_locale_name(int category, const char *locale);
/*
*************** set_locale_and_encoding(ClusterInfo *clu
*** 359,366 ****
i_datcollate = PQfnumber(res, "datcollate");
i_datctype = PQfnumber(res, "datctype");
! ctrl->lc_collate = pg_strdup(PQgetvalue(res, 0, i_datcollate));
! ctrl->lc_ctype = pg_strdup(PQgetvalue(res, 0, i_datctype));
PQclear(res);
}
--- 360,382 ----
i_datcollate = PQfnumber(res, "datcollate");
i_datctype = PQfnumber(res, "datctype");
! if (GET_MAJOR_VERSION(cluster->major_version) < 902)
! {
! /*
! * Pre-9.2 did not canonicalize the supplied locale names
! * to match what the system returns, while 9.2+ does, so
! * convert pre-9.2 to match.
! */
! ctrl->lc_collate = get_canonical_locale_name(LC_COLLATE,
! pg_strdup(PQgetvalue(res, 0, i_datcollate)));
! ctrl->lc_ctype = get_canonical_locale_name(LC_CTYPE,
! pg_strdup(PQgetvalue(res, 0, i_datctype)));
! }
! else
! {
! ctrl->lc_collate = pg_strdup(PQgetvalue(res, 0, i_datcollate));
! ctrl->lc_ctype = pg_strdup(PQgetvalue(res, 0, i_datctype));
! }
PQclear(res);
}
*************** static void
*** 390,405 ****
check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl)
{
! /* These are often defined with inconsistent case, so use pg_strcasecmp(). */
if (pg_strcasecmp(oldctrl->lc_collate, newctrl->lc_collate) != 0)
pg_log(PG_FATAL,
! "old and new cluster lc_collate values do not match\n");
if (pg_strcasecmp(oldctrl->lc_ctype, newctrl->lc_ctype) != 0)
pg_log(PG_FATAL,
! "old and new cluster lc_ctype values do not match\n");
if (pg_strcasecmp(oldctrl->encoding, newctrl->encoding) != 0)
pg_log(PG_FATAL,
! "old and new cluster encoding values do not match\n");
}
--- 406,428 ----
check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl)
{
! /*
! * These are often defined with inconsistent case, so use pg_strcasecmp().
! * They also often use inconsistent hyphenation, which we cannot fix, e.g.
! * UTF-8 vs. UTF8, so at least we display the mismatching values.
! */
if (pg_strcasecmp(oldctrl->lc_collate, newctrl->lc_collate) != 0)
pg_log(PG_FATAL,
! "lc_collate cluster values do not match: old \"%s\", new \"%s\"\n",
! oldctrl->lc_collate, newctrl->lc_collate);
if (pg_strcasecmp(oldctrl->lc_ctype, newctrl->lc_ctype) != 0)
pg_log(PG_FATAL,
! "lc_ctype cluster values do not match: old \"%s\", new \"%s\"\n",
! oldctrl->lc_ctype, newctrl->lc_ctype);
if (pg_strcasecmp(oldctrl->encoding, newctrl->encoding) != 0)
pg_log(PG_FATAL,
! "encoding cluster values do not match: old \"%s\", new \"%s\"\n",
! oldctrl->encoding, newctrl->encoding);
}
*************** get_bin_version(ClusterInfo *cluster)
*** 931,933 ****
--- 954,993 ----
cluster->bin_version = (pre_dot * 100 + post_dot) * 100;
}
+
+
+ /*
+ * get_canonical_locale_name
+ *
+ * Send the locale name to the system, and hope we get back a canonical
+ * version. This should match the backend's check_locale() function.
+ */
+ static char *
+ get_canonical_locale_name(int category, const char *locale)
+ {
+ char *save;
+ char *res;
+
+ save = setlocale(category, NULL);
+ if (!save)
+ pg_log(PG_FATAL, "failed to get the current locale\n");
+
+ /* 'save' may be pointing at a modifiable scratch variable, so copy it. */
+ save = pg_strdup(save);
+
+ /* set the locale with setlocale, to see if it accepts it. */
+ res = setlocale(category, locale);
+
+ if (!res)
+ pg_log(PG_FATAL, "failed to get system local name for \"%s\"\n", res);
+
+ res = pg_strdup(res);
+
+ /* restore old value. */
+ if (!setlocale(category, save))
+ pg_log(PG_FATAL, "failed to restore old locale \"%s\"\n", save);
+
+ free(save);
+
+ return res;
+ }
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
new file mode 100644
index 9d08f41..c406941
*** a/contrib/pg_upgrade/info.c
--- b/contrib/pg_upgrade/info.c
*************** gen_db_file_maps(DbInfo *old_db, DbInfo
*** 40,53 ****
int relnum;
int num_maps = 0;
- if (old_db->rel_arr.nrels != new_db->rel_arr.nrels)
- pg_log(PG_FATAL, "old and new databases \"%s\" have a different number of relations\n",
- old_db->db_name);
-
maps = (FileNameMap *) pg_malloc(sizeof(FileNameMap) *
old_db->rel_arr.nrels);
! for (relnum = 0; relnum < old_db->rel_arr.nrels; relnum++)
{
RelInfo *old_rel = &old_db->rel_arr.rels[relnum];
RelInfo *new_rel = &new_db->rel_arr.rels[relnum];
--- 40,50 ----
int relnum;
int num_maps = 0;
maps = (FileNameMap *) pg_malloc(sizeof(FileNameMap) *
old_db->rel_arr.nrels);
! for (relnum = 0; relnum < Min(old_db->rel_arr.nrels, new_db->rel_arr.nrels);
! relnum++)
{
RelInfo *old_rel = &old_db->rel_arr.rels[relnum];
RelInfo *new_rel = &new_db->rel_arr.rels[relnum];
*************** gen_db_file_maps(DbInfo *old_db, DbInfo
*** 78,83 ****
--- 75,85 ----
num_maps++;
}
+ /* Do this check after the loop so hopefully we will produce a clearer error above */
+ if (old_db->rel_arr.nrels != new_db->rel_arr.nrels)
+ pg_log(PG_FATAL, "old and new databases \"%s\" have a different number of relations\n",
+ old_db->db_name);
+
*nmaps = num_maps;
return maps;
}
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
