From: Bruce Momjian <bruce@momjian.us>
To: Rural Hunter <ruralhunter@gmail.com>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
Date: Tue, 2 Oct 2012 11:58:57 -0400
Message-ID: <20121002155857.GE30089@momjian.us> (raw)
In-Reply-To: <20120925131033.GB4250@momjian.us>
References: <1348440393.18490.2.camel@vanquo.pezone.net>
<20120924125500.GA21242@momjian.us>
<50606726.3050802@gmail.com>
<20120924142659.GC21242@momjian.us>
<5060720E.1080903@gmail.com>
<20120924145731.GE21242@momjian.us>
<5060FDAF.8020407@gmail.com>
<20120925030017.GA4250@momjian.us>
<50613193.4010204@gmail.com>
<20120925131033.GB4250@momjian.us>
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:download all (3 files, .tar.gz)toast.diff (text/x-diff, 4.2K) downloaddiff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.cnew file mode 100644index 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);
locale.diff (text/x-diff, 4.3K) downloaddiff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.cnew file mode 100644index 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;+ }better_error.diff (text/x-diff, 1.5K) downloaddiff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.cnew file mode 100644index 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;
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bruce@momjian.us, ruralhunter@gmail.com, peter_e@gmx.net, pgsql-hackers@postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
In-Reply-To: <20121002155857.GE30089@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox