On Mon, Sep 24, 2012 at 11:04:32AM -0400, Tom Lane wrote:
> Bruce Momjian <br...@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 <br...@momjian.us> 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..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;
+ }
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
