From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Rural Hunter <ruralhunter@gmail.com>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
Date: Mon, 24 Sep 2012 14:51:59 -0400
Message-ID: <20120924185159.GI21242@momjian.us> (raw)
In-Reply-To: <15359.1348499072@sss.pgh.pa.us>
References: <505C307E.20501@gmail.com>
<1348403628.18490.1.camel@vanquo.pezone.net>
<505F1A98.9090901@gmail.com>
<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>
<15359.1348499072@sss.pgh.pa.us>
On Mon, Sep 24, 2012 at 11:04:32AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Well, if you run that query on template0 in the old and new cluster, you
> > will see something different in the two of them. Could you have used
> > default in one and a non-dash in the other. Did we change the way we
> > canonicalize the locale between 9.1 and 9.2?
>
> IIRC, we didn't try to canonicalize locale names at all before 9.2.
> That initdb code you're quoting is of fairly recent vintage.
OK, I have developed two patches.
The first fixes the problem of toast tables having oid >
FirstNormalObjectId due to recreating the information_schema as outlined
in the 9.1 release notes. In fact, there are several cases this fixes,
but information_schema was the one reported. The basic problem is that
TOAST tables can't be restricted by schema -- you have to gather the
relations, and then get the toast tables. The good news is that
pg_upgrade caught its own bug and threw an error.
I was able to test this patch by testing the information_schema
recreation, and I checked to see the regression database had the
expected info.c relation count.
The second patch canonicalizes the old cluster's collation and ctype
values pulled from the template0 database.
I was recreate the fix my Debian Squeeze system. Can someone suggestion
a way? I updated pg_database on the old 9.1 cluster to be en_US.UTF8,
while the new cluster defaults to en_US.UTF-8, but pg_upgrade kept them
the same after the setlocale() call and pg_upgrade threw a mismatch
error.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:download all (2 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, 2.7K) downloaddiff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.cnew file mode 100644index bed10f8..beb177d
*** 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);
}
*************** get_bin_version(ClusterInfo *cluster)
*** 931,933 ****
--- 947,986 ----
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;+ }
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, tgl@sss.pgh.pa.us, ruralhunter@gmail.com, peter_e@gmx.net, pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed
In-Reply-To: <20120924185159.GI21242@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