Dave Byrne wrote: > Beginning with commit 002c105a0706bd1c1e939fe0f47ecdceeae6c52d > pg_upgrade will fail if there are orphaned temp tables in the current > database with the message 'old and new databases "postgres" have a > different number of relations' > > On line 41 of pg_upgrade/info.c pg_upgrade checks that the number of > relations are the same but includes orphaned temp tables in the comparison. > > Is this expected behavior? If so is there a more detailed error message > that can be added explain the cause of the failure? It wasn't evident > until modified pg_upgrade to show the missing oid's and recognized them > as temp tables with oid2name.
Thanks for your report and patch. Let me give some background on pg_upgrade to explain what is happening. Pg_upgrade uses two C arrays to store information about tables and indexes for the old and new clusters. It is not possible to store this information in a database because both clusters are down when pg_upgrade needs to use this information. In pre-9.1 pg_upgrade, pg_upgrade did a sequential scan of the arrays looking for a match between old and new cluster objects. This was reported as slow for databases with many objects, and I could reproduce the slowness. I added some caching in 9.0 but the real solution for 9.1 was to assume a one-to-one mapping between the old and new C arrays, i.e. the 5th entry in the old cluster array is the same as the 5th element in the new cluster array. I knew this was risky but was the right solution so it doesn't surprise me you found a failure. pg_upgrade checks that the size of the two arrays in the same and also checks that each element matches --- the former is what generated your error. Now, about the cause. I had not anticipated that orphaned temp objects could exist in either cluster. In fact, this case would have generated an error in 9.0 as well, but with a different error message. Looking futher, pg_upgrade has to use the same object filter as pg_dump, and pg_dump uses this C test: pg_dump.c: else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || pg_dumpall uses this filter: "WHERE spcname !~ '^pg_' " The problem is that the filter used by pg_upgrade only excluded pg_catalog, not pg_temp* as well. I have developed the attached two patches, one for 9.0, and the second for 9.1 and 9.2 which will make pg_upgrade now match the pg_dump filtering and produce proper results for orphaned temp tables by filtering them. As far as unlogged tables, those are dumped by 9.1/9.2, so there is no need to check relpersistence in this patch. pg_dump doesn't check relistemp either. -- 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 567c64e..ca357e7 *** a/contrib/pg_upgrade/info.c --- b/contrib/pg_upgrade/info.c *************** get_rel_infos(migratorContext *ctx, cons *** 326,332 **** " ON c.relnamespace = n.oid " " LEFT OUTER JOIN pg_catalog.pg_tablespace t " " ON c.reltablespace = t.oid " ! "WHERE (( n.nspname NOT IN ('pg_catalog', 'information_schema') " " AND c.oid >= %u " " ) OR ( " " n.nspname = 'pg_catalog' " --- 326,335 ---- " ON c.relnamespace = n.oid " " LEFT OUTER JOIN pg_catalog.pg_tablespace t " " ON c.reltablespace = t.oid " ! "WHERE (( " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' " ! " AND n.nspname != 'information_schema' " " AND c.oid >= %u " " ) OR ( " " n.nspname = 'pg_catalog' " diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c new file mode 100644 index 6ca266c..930f76d *** a/contrib/pg_upgrade/version_old_8_3.c --- b/contrib/pg_upgrade/version_old_8_3.c *************** old_8_3_check_for_name_data_type_usage(m *** 61,67 **** " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); --- 61,68 ---- " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); *************** old_8_3_check_for_tsquery_usage(migrator *** 151,157 **** " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); --- 152,159 ---- " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); *************** old_8_3_rebuild_tsvector_tables(migrator *** 250,256 **** " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); /* --- 252,259 ---- " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); /* *************** old_8_3_rebuild_tsvector_tables(migrator *** 268,274 **** " NOT a.attisdropped AND " \ " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \ " c.relnamespace = n.oid AND " \ ! " n.nspname != 'pg_catalog' AND " \ " n.nspname != 'information_schema') " ntups = PQntuples(res); --- 271,277 ---- " NOT a.attisdropped AND " \ " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \ " c.relnamespace = n.oid AND " \ ! " n.nspname !~ '^pg_' AND " \ " n.nspname != 'information_schema') " ntups = PQntuples(res); *************** old_8_3_create_sequence_script(migratorC *** 638,644 **** " pg_catalog.pg_namespace n " "WHERE c.relkind = 'S' AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); --- 641,648 ---- " pg_catalog.pg_namespace n " "WHERE c.relkind = 'S' AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res);
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c new file mode 100644 index 72bc489..3ef3429 *** a/contrib/pg_upgrade/info.c --- b/contrib/pg_upgrade/info.c *************** get_rel_infos(ClusterInfo *cluster, DbIn *** 266,272 **** " LEFT OUTER JOIN pg_catalog.pg_tablespace t " " ON c.reltablespace = t.oid " "WHERE relkind IN ('r','t', 'i'%s) 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) )) " --- 266,274 ---- " LEFT OUTER JOIN pg_catalog.pg_tablespace t " " ON c.reltablespace = t.oid " "WHERE relkind IN ('r','t', 'i'%s) AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " ((n.nspname !~ '^pg_' AND " ! " n.nspname NOT IN ('information_schema', 'binary_upgrade') AND " " c.oid >= %u) " " OR (n.nspname = 'pg_catalog' AND " " relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) )) " diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c new file mode 100644 index 43bfdc1..1c736d2 *** a/contrib/pg_upgrade/version_old_8_3.c --- b/contrib/pg_upgrade/version_old_8_3.c *************** old_8_3_check_for_name_data_type_usage(C *** 59,65 **** " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); --- 59,66 ---- " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); *************** old_8_3_check_for_tsquery_usage(ClusterI *** 148,154 **** " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); --- 149,156 ---- " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); *************** old_8_3_rebuild_tsvector_tables(ClusterI *** 245,251 **** " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); /* --- 247,254 ---- " NOT a.attisdropped AND " " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); /* *************** old_8_3_rebuild_tsvector_tables(ClusterI *** 263,269 **** " NOT a.attisdropped AND " \ " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \ " c.relnamespace = n.oid AND " \ ! " n.nspname != 'pg_catalog' AND " \ " n.nspname != 'information_schema') " ntups = PQntuples(res); --- 266,272 ---- " NOT a.attisdropped AND " \ " a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \ " c.relnamespace = n.oid AND " \ ! " n.nspname !~ '^pg_' AND " \ " n.nspname != 'information_schema') " ntups = PQntuples(res); *************** old_8_3_create_sequence_script(ClusterIn *** 616,622 **** " pg_catalog.pg_namespace n " "WHERE c.relkind = 'S' AND " " c.relnamespace = n.oid AND " ! " n.nspname != 'pg_catalog' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res); --- 619,626 ---- " pg_catalog.pg_namespace n " "WHERE c.relkind = 'S' AND " " c.relnamespace = n.oid AND " ! /* exclude pg_catalog and pg_temp_ (could be orphaned tables) */ ! " n.nspname !~ '^pg_' AND " " n.nspname != 'information_schema'"); ntups = PQntuples(res);
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers