Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGDlE-0003o3-2O for pgsql-hackers@postgresql.org; Mon, 24 Sep 2012 18:52:04 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TGDlC-00083Q-6s for pgsql-hackers@postgresql.org; Mon, 24 Sep 2012 18:52:03 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1TGDl9-0005x8-Fq; Mon, 24 Sep 2012 14:51:59 -0400 Date: Mon, 24 Sep 2012 14:51:59 -0400 From: Bruce Momjian To: Tom Lane Cc: Rural Hunter , Peter Eisentraut , PostgreSQL-development Subject: Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed Message-ID: <20120924185159.GI21242@momjian.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> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="AhhlLboLdkugWU4S" Content-Disposition: inline In-Reply-To: <15359.1348499072@sss.pgh.pa.us> User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201209/902 X-Sequence-Number: 213930 --AhhlLboLdkugWU4S Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Sep 24, 2012 at 11:04:32AM -0400, Tom Lane wrote: > Bruce Momjian 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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + --AhhlLboLdkugWU4S Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="toast.diff" 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); --AhhlLboLdkugWU4S Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="locale.diff" diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c new file mode 100644 index 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; + } --AhhlLboLdkugWU4S--