Thread: pg_upgrade issues
PostgreSQL 9.0 beta 2 Windows XP Professional SP2 While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of = the Postgres project), the following issues came up: 1. When using the --logfile option, pg_upgrade quits with an error like thi= s: The process cannot access the file because it is being used by another proc= ess. There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l "pg_upgrad= e.log " -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c autovacuum=3Doff -c autovacu= um_free ze_max_age=3D2000000000" start >> "pg_upgrade.log" 2>&1" 2. Although pg_upgrade has a username option, this option is not used when = pg_dump is called, which tries to use the current logon account name as the= user name. As a result, pg_upgrade can only be used when the command shell= is launched under the postgres account. (I know that this is mentioned on = the doc page, but this doesn't seem right). 3. The old database had the pgadmin debugger installed. The module is part = of 8.4 and 9.0 distributions for Windows. However, pg_upgrade reported the = following error: ERROR: could not load library "C:/PostgreSQL/9.0/lib/pldbgapi.dll": The sp= ecified module could not be found. I had to uninstall the debugger from the old database before I could procee= d.
Another issue: 4. The --link option doesn't seem to work on Windows: pg_upgrade still copi= es data from the old cluster to the new. There doesn't appear to be a way t= o upgrade a database on Windows without copying the entire uncompressed dat= abase, which can be a problem where disk space is limited.
depstein@alliedtesting.com wrote:
BBBBBB> PostgreSQL 9.0 beta 2
> Windows XP Professional SP2
>
> While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of the Postgres project), the following
issuescame up:
>
> 1. When using the --logfile option, pg_upgrade quits with an error like this:
>
> The process cannot access the file because it is being used by another process.
>
> There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l "pg_upgrade.log
> " -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c autovacuum=off -c autovacuum_free
> ze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1"
Hiroshi, can you comment on the above bug report? I was able to
reproduce this on XP. I think we added -l for Win32 because the code
says:
/* use -l for Win32 */
snprintf(cmd, sizeof(cmd),
SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
but I don't remember the details, and cvs.pgfoundry.org is down right
now. Thanks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Hi. Ooops, I can't follow your quick thread.... sorry, It will be a weekend if allowed. Regards, Hiroshi Saito ----- Original Message ----- From: "Bruce Momjian" <bruce@momjian.us> > depstein@alliedtesting.com wrote: > BBBBBB> PostgreSQL 9.0 beta 2 >> Windows XP Professional SP2 >> >> While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of the Postgres >> project), the following issues came up: >> >> 1. When using the --logfile option, pg_upgrade quits with an error like this: >> >> The process cannot access the file because it is being used by another process. >> >> There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l "pg_upgrade.log >> " -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c autovacuum=off -c autovacuum_free >> ze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1" > > Hiroshi, can you comment on the above bug report? I was able to > reproduce this on XP. I think we added -l for Win32 because the code > says: > > /* use -l for Win32 */ > snprintf(cmd, sizeof(cmd), > SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" " > > but I don't remember the details, and cvs.pgfoundry.org is down right > now. Thanks. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + None of us is going to be here forever. +
Hiroshi Saito wrote: > Hi. > > Ooops, I can't follow your quick thread.... > sorry, It will be a weekend if allowed. I have replied and I think I have it fixed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
Encountered another problem with pg_upgrade on Windows XP Pro: I was trying to migrate from 8.4 to 9.0beta2 without linking, and apparentl= y there was not enough space on the hard drive. However, pg_upgrade didn't = report any problems, and it looked for all the world as if everything went = well. I only found out that not all files were copied to the new cluster wh= en vacuumdb reported missing files and when I actually compared the sizes o= f the two clusters on the disk.
depstein@alliedtesting.com wrote: > Encountered another problem with pg_upgrade on Windows XP Pro: > > I was trying to migrate from 8.4 to 9.0beta2 without linking, and > apparently there was not enough space on the hard drive. However, > pg_upgrade didn't report any problems, and it looked for all the world > as if everything went well. I only found out that not all files were > copied to the new cluster when vacuumdb reported missing files and when > I actually compared the sizes of the two clusters on the disk. Thank you for the clear bug report. Magnus has diagnosed the problem, and I am attaching the patch fix that will appear in 9.0 beta4. Fortunately this problem only happens in copy mode, and only when the copy fails, as you saw. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: contrib/pg_upgrade/file.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/file.c,v retrieving revision 1.13 diff -c -c -r1.13 file.c *** contrib/pg_upgrade/file.c 6 Jul 2010 19:18:55 -0000 1.13 --- contrib/pg_upgrade/file.c 9 Jul 2010 16:41:46 -0000 *************** *** 170,175 **** --- 170,177 ---- if (nbytes < 0) { + int save_errno = errno; + if (buffer != NULL) free(buffer); *************** *** 179,184 **** --- 181,187 ---- if (dest_fd != 0) close(dest_fd); + errno = save_errno; return -1; } *************** *** 190,197 **** if (write(dest_fd, buffer, nbytes) != nbytes) { /* if write didn't set errno, assume problem is no disk space */ ! if (errno == 0) ! errno = ENOSPC; if (buffer != NULL) free(buffer); --- 193,199 ---- if (write(dest_fd, buffer, nbytes) != nbytes) { /* if write didn't set errno, assume problem is no disk space */ ! int save_errno = errno ? errno : ENOSPC; if (buffer != NULL) free(buffer); *************** *** 202,207 **** --- 204,210 ---- if (dest_fd != 0) close(dest_fd); + errno = save_errno; return -1; } }
I have encountered another problem with pg_upgrade, while migrating from 8.= 4 to 9.0 (beta2, as well as beta3) on Windows XP Pro. I have a table with a regclass column, which references other tables in the= same database: CREATE TABLE common_inst.reg_asset ( asset_id integer NOT NULL, table_name regclass, CONSTRAINT asset_registered_pkey PRIMARY KEY (asset_id) ) Sometimes after I migrate the database, the values in the table_name column= show integer numbers (e.g. '284551' for a table named 'common_inst.asset_s= pot_equity_index') instead of table references. These numbers are the OIDs = of the tables in the old database, but in the new database these OIDs have = no referent. FWIW, when looking at the pg_class entries for the referenced tables, I hav= e noticed that in the old database the table OID and the column relfilenode= have different values. In the migrated database the values are the same an= d coincide with relfilenode in the old database. For example, Old database: Table name: common_inst.asset_spot_equity_index pg_class.oid =3D 284551 pg_class.relfilenode =3D 288011 Migrated database: Table name: common_inst.asset_spot_equity_index pg_class.oid =3D 288011 pg_class.relfilenode =3D 288011 I am trying to obtain a binary dump of a small test database where this iss= ue could be reproduced, but so far, no luck. At present, the least such dat= abase is 1.5 GB compressed and contains a lot of proprietary info. I would = welcome any suggestions on how to do this. Thanks, Dmitry
depstein@alliedtesting.com wrote: > I have encountered another problem with pg_upgrade, while migrating > from 8.4 to 9.0 (beta2, as well as beta3) on Windows XP Pro. Wow, your testing of pg_upgrade has been excellent! I hope you can continue and test other areas of our system too. I am actually curious how you are so good at this. > I have a table with a regclass column, which references other tables > in the same database: > > CREATE TABLE common_inst.reg_asset > ( > asset_id integer NOT NULL, > table_name regclass, > CONSTRAINT asset_registered_pkey PRIMARY KEY (asset_id) > ) > > Sometimes after I migrate the database, the values in the table_name > column show integer numbers (e.g. '284551' for a table named > 'common_inst.asset_spot_equity_index') instead of table references. > These numbers are the OIDs of the tables in the old database, but in > the new database these OIDs have no referent. Ah, I never thought of the migrations issues of user tables using the reg* data types: pg_catalog | regclass | registered class pg_catalog | regconfig | registered text search configuration pg_catalog | regdictionary | registered text search dictionary pg_catalog | regoper | registered operator pg_catalog | regoperator | registered operator (with args) pg_catalog | regproc | registered procedure pg_catalog | regprocedure | registered procedure (with args) pg_catalog | regtype | registered type In fact, I never even considered that user tables would be using these data types. The basic problem is that we don't preserve most of these oids when recreating them in the new cluster --- we only preserve pg_type.oid, pg_class.relfilenode, and pg_enum.oid. > FWIW, when looking at the pg_class entries for the referenced tables, > I have noticed that in the old database the table OID and the column > relfilenode have different values. In the migrated database the values > are the same and coincide with relfilenode in the old database. > > For example, > > Old database: > > Table name: common_inst.asset_spot_equity_index > pg_class.oid = 284551 > pg_class.relfilenode = 288011 > > Migrated database: > > Table name: common_inst.asset_spot_equity_index > pg_class.oid = 288011 > pg_class.relfilenode = 288011 > > > I am trying to obtain a binary dump of a small test database where this > issue could be reproduced, but so far, no luck. At present, the least > such database is 1.5 GB compressed and contains a lot of proprietary > info. I would welcome any suggestions on how to do this. Your diagnosis is 100% on target, and very perceptive. Because we preserve pg_class.relfilenode, if the table has not been rebuilt, for example by CLUSTER, the old system the pg_class.oid and pg_class.relfilenode are the same, and hence pg_class.oid is preserved through pg_class.relfilenode during the migration. If they are different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the oid has changed, and you will see the errors you are reporting. I am inclined to prevent pg_upgrade from migrating any database that uses any of these reg* data types, and document this restriction. I probably could allow regtype because that pg_type is preserved. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
Bruce Momjian wrote: > depstein@alliedtesting.com wrote: > > I am trying to obtain a binary dump of a small test database where this > > issue could be reproduced, but so far, no luck. At present, the least > > such database is 1.5 GB compressed and contains a lot of proprietary > > info. I would welcome any suggestions on how to do this. > > Your diagnosis is 100% on target, and very perceptive. Because we > preserve pg_class.relfilenode, if the table has not been rebuilt, for > example by CLUSTER, the old system the pg_class.oid and > pg_class.relfilenode are the same, and hence pg_class.oid is preserved > through pg_class.relfilenode during the migration. If they are > different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the > oid has changed, and you will see the errors you are reporting. > > I am inclined to prevent pg_upgrade from migrating any database that > uses any of these reg* data types, and document this restriction. I > probably could allow regtype because that pg_type is preserved. I have applied the attached patch to CVS HEAD and 9.0 that prevent migration when any reg* data type is used in a user table (except regtype because pg_type.oid is preserved). I documented this restriction. Thanks again for the report. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: contrib/pg_upgrade/check.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/check.c,v retrieving revision 1.12 diff -c -c -r1.12 check.c *** contrib/pg_upgrade/check.c 13 Jul 2010 15:56:53 -0000 1.12 --- contrib/pg_upgrade/check.c 25 Jul 2010 03:19:48 -0000 *************** *** 14,19 **** --- 14,20 ---- static void check_new_db_is_empty(migratorContext *ctx); static void check_locale_and_encoding(migratorContext *ctx, ControlData *oldctrl, ControlData *newctrl); + static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster); void *************** *** 61,71 **** * Check for various failure cases */ ! old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD); /* old = PG 8.3 checks? */ if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803) { old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD); old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD); if (ctx->check) --- 62,73 ---- * Check for various failure cases */ ! check_for_reg_data_type_usage(ctx, CLUSTER_OLD); /* old = PG 8.3 checks? */ if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803) { + old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD); old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD); old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD); if (ctx->check) *************** *** 439,441 **** --- 441,544 ---- check_ok(ctx); } + + + /* + * check_for_reg_data_type_usage() + * pg_upgrade only preserves these system values: + * pg_class.relfilenode + * pg_type.oid + * pg_enum.oid + * + * Most of the reg* data types reference system catalog info that is + * not preserved, and hence these data types cannot be used in user + * tables upgraded by pg_upgrade. + */ + void + check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster) + { + ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ? + &ctx->old : &ctx->new; + int dbnum; + FILE *script = NULL; + bool found = false; + char output_path[MAXPGPATH]; + + prep_status(ctx, "Checking for reg* system oid user data types"); + + snprintf(output_path, sizeof(output_path), "%s/tables_using_reg.txt", + ctx->cwd); + + for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++) + { + PGresult *res; + bool db_used = false; + int ntups; + int rowno; + int i_nspname, + i_relname, + i_attname; + DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum]; + PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster); + + res = executeQueryOrDie(ctx, conn, + "SELECT n.nspname, c.relname, a.attname " + "FROM pg_catalog.pg_class c, " + " pg_catalog.pg_namespace n, " + " pg_catalog.pg_attribute a " + "WHERE c.oid = a.attrelid AND " + " NOT a.attisdropped AND " + " a.atttypid IN ( " + " 'pg_catalog.regproc'::pg_catalog.regtype, " + " 'pg_catalog.regprocedure'::pg_catalog.regtype, " + " 'pg_catalog.regoper'::pg_catalog.regtype, " + " 'pg_catalog.regoperator'::pg_catalog.regtype, " + " 'pg_catalog.regclass'::pg_catalog.regtype, " + /* regtype.oid is preserved, so 'regtype' is OK */ + " 'pg_catalog.regconfig'::pg_catalog.regtype, " + " 'pg_catalog.regdictionary'::pg_catalog.regtype) AND " + " c.relnamespace = n.oid AND " + " n.nspname != 'pg_catalog' AND " + " n.nspname != 'information_schema'"); + + ntups = PQntuples(res); + i_nspname = PQfnumber(res, "nspname"); + i_relname = PQfnumber(res, "relname"); + i_attname = PQfnumber(res, "attname"); + for (rowno = 0; rowno < ntups; rowno++) + { + found = true; + if (script == NULL && (script = fopen(output_path, "w")) == NULL) + pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path); + if (!db_used) + { + fprintf(script, "Database: %s\n", active_db->db_name); + db_used = true; + } + fprintf(script, " %s.%s.%s\n", + PQgetvalue(res, rowno, i_nspname), + PQgetvalue(res, rowno, i_relname), + PQgetvalue(res, rowno, i_attname)); + } + + PQclear(res); + + PQfinish(conn); + } + + if (found) + { + fclose(script); + pg_log(ctx, PG_REPORT, "fatal\n"); + pg_log(ctx, PG_FATAL, + "| Your installation contains one of the reg* data types in\n" + "| user tables. These data types reference system oids that\n" + "| are not preserved by pg_upgrade, so this cluster cannot\n" + "| currently be upgraded. You can remove the problem tables\n" + "| and restart the migration. A list of the problem columns\n" + "| is in the file:\n" + "| \t%s\n\n", output_path); + } + else + check_ok(ctx); + } Index: contrib/pg_upgrade/function.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/function.c,v retrieving revision 1.6 diff -c -c -r1.6 function.c *** contrib/pg_upgrade/function.c 3 Jul 2010 16:33:14 -0000 1.6 --- contrib/pg_upgrade/function.c 25 Jul 2010 03:19:48 -0000 *************** *** 253,259 **** fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation uses loadable libraries that are missing\n" "| from the new installation. You can add these libraries to\n" "| the new installation, or remove the functions using them\n" "| from the old installation. A list of the problem libraries\n" --- 253,259 ---- fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation references loadable libraries that are missing\n" "| from the new installation. You can add these libraries to\n" "| the new installation, or remove the functions using them\n" "| from the old installation. A list of the problem libraries\n" Index: contrib/pg_upgrade/version_old_8_3.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/version_old_8_3.c,v retrieving revision 1.6 diff -c -c -r1.6 version_old_8_3.c *** contrib/pg_upgrade/version_old_8_3.c 3 Jul 2010 16:33:14 -0000 1.6 --- contrib/pg_upgrade/version_old_8_3.c 25 Jul 2010 03:19:48 -0000 *************** *** 94,100 **** fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation uses the \"name\" data type in\n" "| user tables. This data type changed its internal\n" "| alignment between your old and new clusters so this\n" "| cluster cannot currently be upgraded. You can\n" --- 94,100 ---- fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation contains the \"name\" data type in\n" "| user tables. This data type changed its internal\n" "| alignment between your old and new clusters so this\n" "| cluster cannot currently be upgraded. You can\n" *************** *** 184,190 **** fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation uses the \"tsquery\" data type.\n" "| This data type added a new internal field between\n" "| your old and new clusters so this cluster cannot\n" "| currently be upgraded. You can remove the problem\n" --- 184,190 ---- fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation contains the \"tsquery\" data type.\n" "| This data type added a new internal field between\n" "| your old and new clusters so this cluster cannot\n" "| currently be upgraded. You can remove the problem\n" *************** *** 274,280 **** fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation uses \"/contrib/isn\" functions\n" "| which rely on the bigint data type. Your old and\n" "| new clusters pass bigint values differently so this\n" "| cluster cannot currently be upgraded. You can\n" --- 274,280 ---- fclose(script); pg_log(ctx, PG_REPORT, "fatal\n"); pg_log(ctx, PG_FATAL, ! "| Your installation contains \"/contrib/isn\" functions\n" "| which rely on the bigint data type. Your old and\n" "| new clusters pass bigint values differently so this\n" "| cluster cannot currently be upgraded. You can\n" Index: doc/src/sgml/pgupgrade.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/pgupgrade.sgml,v retrieving revision 1.12 diff -c -c -r1.12 pgupgrade.sgml *** doc/src/sgml/pgupgrade.sgml 25 May 2010 15:55:28 -0000 1.12 --- doc/src/sgml/pgupgrade.sgml 25 Jul 2010 03:19:49 -0000 *************** *** 445,453 **** </listitem> </orderedlist> ! </sect2> ! <sect2> <title>Limitations in migrating <emphasis>from</> PostgreSQL 8.3</title> <para> --- 445,453 ---- </listitem> </orderedlist> ! </sect2> ! <sect2> <title>Limitations in migrating <emphasis>from</> PostgreSQL 8.3</title> <para> *************** *** 514,529 **** version 8.4 or later of the one-click distribution. It is not possible to upgrade from the MSI installer to the one-click installer. </para> <para> ! All failure, rebuild, and reindex cases will be reported by <application>pg_upgrade</> ! if they affect your installation; post-migration scripts to rebuild ! tables and indexes will be automatically generated. </para> <para> For deployment testing, create a schema-only copy of the old cluster, ! insert dummy data, and migrate that. </para> <para> --- 514,543 ---- version 8.4 or later of the one-click distribution. It is not possible to upgrade from the MSI installer to the one-click installer. </para> + + </sect2> + + <sect2> + <title>Notes</title> + + <para> + <application>pg_upgrade</> does not support migration of databases + containing these reg* system oid-referencing data types: + <type>regproc</>, <type>regprocedure</>, <type>regoper</>, + <type>regoperator</>, <type>regclass</>, <type>regconfig</>, and + <type>regdictionary</>. (<type>regtype</> can be migrated.) + </para> <para> ! All failure, rebuild, and reindex cases will be reported by ! <application>pg_upgrade</> if they affect your installation; ! post-migration scripts to rebuild tables and indexes will be ! generated automatically. </para> <para> For deployment testing, create a schema-only copy of the old cluster, ! insert dummy data, and migrate that. </para> <para>
Bruce Momjian wrote: > I have applied the attached patch to CVS HEAD and 9.0 that prevent > migration when any reg* data type is used in a user table (except > regtype because pg_type.oid is preserved). > > I documented this restriction. Thanks again for the report. Attached is a secondary patch for /contrib/isn, in case you want that too. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: contrib/pg_upgrade/check.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/check.c,v retrieving revision 1.13 diff -c -c -r1.13 check.c *** contrib/pg_upgrade/check.c 25 Jul 2010 03:28:32 -0000 1.13 --- contrib/pg_upgrade/check.c 25 Jul 2010 03:43:07 -0000 *************** *** 14,19 **** --- 14,21 ---- static void check_new_db_is_empty(migratorContext *ctx); static void check_locale_and_encoding(migratorContext *ctx, ControlData *oldctrl, ControlData *newctrl); + static void check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, + Cluster whichCluster); static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster); *************** *** 63,73 **** */ check_for_reg_data_type_usage(ctx, CLUSTER_OLD); /* old = PG 8.3 checks? */ if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803) { - old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD); old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD); old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD); if (ctx->check) --- 65,75 ---- */ check_for_reg_data_type_usage(ctx, CLUSTER_OLD); + check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD); /* old = PG 8.3 checks? */ if (GET_MAJOR_VERSION(ctx->old.major_version) <= 803) { old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD); old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD); if (ctx->check) *************** *** 444,449 **** --- 446,543 ---- /* + * check_for_isn_and_int8_passing_mismatch() + * + * /contrib/isn relies on data type int8, and in 8.4 int8 can now be passed + * by value. The schema dumps the CREATE TYPE PASSEDBYVALUE setting so + * it must match for the old and new servers. + */ + void + check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, Cluster whichCluster) + { + ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ? + &ctx->old : &ctx->new; + int dbnum; + FILE *script = NULL; + bool found = false; + char output_path[MAXPGPATH]; + + prep_status(ctx, "Checking for /contrib/isn with bigint-passing mismatch"); + + if (ctx->old.controldata.float8_pass_by_value == + ctx->new.controldata.float8_pass_by_value) + { + /* no mismatch */ + check_ok(ctx); + return; + } + + snprintf(output_path, sizeof(output_path), "%s/contrib_isn_and_int8_pass_by_value.txt", + ctx->cwd); + + for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++) + { + PGresult *res; + bool db_used = false; + int ntups; + int rowno; + int i_nspname, + i_proname; + DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum]; + PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster); + + /* Find any functions coming from contrib/isn */ + res = executeQueryOrDie(ctx, conn, + "SELECT n.nspname, p.proname " + "FROM pg_catalog.pg_proc p, " + " pg_catalog.pg_namespace n " + "WHERE p.pronamespace = n.oid AND " + " p.probin = '$libdir/isn'"); + + ntups = PQntuples(res); + i_nspname = PQfnumber(res, "nspname"); + i_proname = PQfnumber(res, "proname"); + for (rowno = 0; rowno < ntups; rowno++) + { + found = true; + if (script == NULL && (script = fopen(output_path, "w")) == NULL) + pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path); + if (!db_used) + { + fprintf(script, "Database: %s\n", active_db->db_name); + db_used = true; + } + fprintf(script, " %s.%s\n", + PQgetvalue(res, rowno, i_nspname), + PQgetvalue(res, rowno, i_proname)); + } + + PQclear(res); + + PQfinish(conn); + } + + if (found) + { + fclose(script); + pg_log(ctx, PG_REPORT, "fatal\n"); + pg_log(ctx, PG_FATAL, + "| Your installation contains \"/contrib/isn\" functions\n" + "| which rely on the bigint data type. Your old and\n" + "| new clusters pass bigint values differently so this\n" + "| cluster cannot currently be upgraded. You can\n" + "| manually migrate data that use \"/contrib/isn\"\n" + "| facilities and remove \"/contrib/isn\" from the\n" + "| old cluster and restart the migration. A list\n" + "| of the problem functions is in the file:\n" + "| \t%s\n\n", output_path); + } + else + check_ok(ctx); + } + + + /* * check_for_reg_data_type_usage() * pg_upgrade only preserves these system values: * pg_class.relfilenode Index: contrib/pg_upgrade/pg_upgrade.h =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/pg_upgrade.h,v retrieving revision 1.15 diff -c -c -r1.15 pg_upgrade.h *** contrib/pg_upgrade/pg_upgrade.h 6 Jul 2010 19:18:55 -0000 1.15 --- contrib/pg_upgrade/pg_upgrade.h 25 Jul 2010 03:43:07 -0000 *************** *** 388,395 **** Cluster whichCluster); void old_8_3_check_for_tsquery_usage(migratorContext *ctx, Cluster whichCluster); - void old_8_3_check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, - Cluster whichCluster); void old_8_3_rebuild_tsvector_tables(migratorContext *ctx, bool check_mode, Cluster whichCluster); void old_8_3_invalidate_hash_gin_indexes(migratorContext *ctx, --- 388,393 ---- Index: contrib/pg_upgrade/version_old_8_3.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/version_old_8_3.c,v retrieving revision 1.7 diff -c -c -r1.7 version_old_8_3.c *** contrib/pg_upgrade/version_old_8_3.c 25 Jul 2010 03:28:32 -0000 1.7 --- contrib/pg_upgrade/version_old_8_3.c 25 Jul 2010 03:43:07 -0000 *************** *** 198,295 **** /* - * old_8_3_check_for_isn_and_int8_passing_mismatch() - * 8.3 -> 8.4 - * /contrib/isn relies on data type int8, and in 8.4 int8 is now passed - * by value. The schema dumps the CREATE TYPE PASSEDBYVALUE setting so - * it must match for the old and new servers. - */ - void - old_8_3_check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, Cluster whichCluster) - { - ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ? - &ctx->old : &ctx->new; - int dbnum; - FILE *script = NULL; - bool found = false; - char output_path[MAXPGPATH]; - - prep_status(ctx, "Checking for /contrib/isn with bigint-passing mismatch"); - - if (ctx->old.controldata.float8_pass_by_value == - ctx->new.controldata.float8_pass_by_value) - { - /* no mismatch */ - check_ok(ctx); - return; - } - - snprintf(output_path, sizeof(output_path), "%s/contrib_isn_and_int8_pass_by_value.txt", - ctx->cwd); - - for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++) - { - PGresult *res; - bool db_used = false; - int ntups; - int rowno; - int i_nspname, - i_proname; - DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum]; - PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster); - - /* Find any functions coming from contrib/isn */ - res = executeQueryOrDie(ctx, conn, - "SELECT n.nspname, p.proname " - "FROM pg_catalog.pg_proc p, " - " pg_catalog.pg_namespace n " - "WHERE p.pronamespace = n.oid AND " - " p.probin = '$libdir/isn'"); - - ntups = PQntuples(res); - i_nspname = PQfnumber(res, "nspname"); - i_proname = PQfnumber(res, "proname"); - for (rowno = 0; rowno < ntups; rowno++) - { - found = true; - if (script == NULL && (script = fopen(output_path, "w")) == NULL) - pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path); - if (!db_used) - { - fprintf(script, "Database: %s\n", active_db->db_name); - db_used = true; - } - fprintf(script, " %s.%s\n", - PQgetvalue(res, rowno, i_nspname), - PQgetvalue(res, rowno, i_proname)); - } - - PQclear(res); - - PQfinish(conn); - } - - if (found) - { - fclose(script); - pg_log(ctx, PG_REPORT, "fatal\n"); - pg_log(ctx, PG_FATAL, - "| Your installation contains \"/contrib/isn\" functions\n" - "| which rely on the bigint data type. Your old and\n" - "| new clusters pass bigint values differently so this\n" - "| cluster cannot currently be upgraded. You can\n" - "| manually migrate data that use \"/contrib/isn\"\n" - "| facilities and remove \"/contrib/isn\" from the\n" - "| old cluster and restart the migration. A list\n" - "| of the problem functions is in the file:\n" - "| \t%s\n\n", output_path); - } - else - check_ok(ctx); - } - - - /* * old_8_3_rebuild_tsvector_tables() * 8.3 -> 8.4 * 8.3 sorts lexemes by its length and if lengths are the same then it uses --- 198,203 ----
> Bruce Momjian wrote: > > depstein@alliedtesting.com wrote: > > > I am trying to obtain a binary dump of a small test database where > > > this issue could be reproduced, but so far, no luck. At present, > the > > > least such database is 1.5 GB compressed and contains a lot of > > > proprietary info. I would welcome any suggestions on how to do > this. > > > > Your diagnosis is 100% on target, and very perceptive. Because we > > preserve pg_class.relfilenode, if the table has not been rebuilt, for > > example by CLUSTER, the old system the pg_class.oid and > > pg_class.relfilenode are the same, and hence pg_class.oid is > preserved > > through pg_class.relfilenode during the migration. If they are > > different, e.g. they ran CLUSTER, pg_upgrade will be wrong because > the > > oid has changed, and you will see the errors you are reporting. > > > > I am inclined to prevent pg_upgrade from migrating any database that > > uses any of these reg* data types, and document this restriction. I > > probably could allow regtype because that pg_type is preserved. >=20 > I have applied the attached patch to CVS HEAD and 9.0 that prevent > migration when any reg* data type is used in a user table (except > regtype because pg_type.oid is preserved). >=20 > I documented this restriction. Thanks again for the report. Thank you for the explanation and the swift action. I just want to note that one reason regclass may be used in user tables (as= opposed to, say, regtype) is that in PL/pgSQL trigger procedures there is = a special variable TG_RELID, which provides a convenient reference to the t= able that pulled the trigger (this is the case for some of our uses). Dmitry
depstein@alliedtesting.com wrote: > > Bruce Momjian wrote: > > > depstein@alliedtesting.com wrote: > > > > I am trying to obtain a binary dump of a small test database where > > > > this issue could be reproduced, but so far, no luck. At present, > > the > > > > least such database is 1.5 GB compressed and contains a lot of > > > > proprietary info. I would welcome any suggestions on how to do > > this. > > > > > > Your diagnosis is 100% on target, and very perceptive. Because we > > > preserve pg_class.relfilenode, if the table has not been rebuilt, for > > > example by CLUSTER, the old system the pg_class.oid and > > > pg_class.relfilenode are the same, and hence pg_class.oid is > > preserved > > > through pg_class.relfilenode during the migration. If they are > > > different, e.g. they ran CLUSTER, pg_upgrade will be wrong because > > the > > > oid has changed, and you will see the errors you are reporting. > > > > > > I am inclined to prevent pg_upgrade from migrating any database that > > > uses any of these reg* data types, and document this restriction. I > > > probably could allow regtype because that pg_type is preserved. > > > > I have applied the attached patch to CVS HEAD and 9.0 that prevent > > migration when any reg* data type is used in a user table (except > > regtype because pg_type.oid is preserved). > > > > I documented this restriction. Thanks again for the report. > > Thank you for the explanation and the swift action. > > I just want to note that one reason regclass may be used in user tables > (as opposed to, say, regtype) is that in PL/pgSQL trigger procedures > there is a special variable TG_RELID, which provides a convenient > reference to the table that pulled the trigger (this is the case for > some of our uses). OK, thanks. I was curious about your usage so I could determine how widespread usage of those reg* types is. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Sat, Jul 24, 2010 at 11:37 PM, Bruce Momjian <bruce@momjian.us> wrote: >> I am inclined to prevent pg_upgrade from migrating any database that >> uses any of these reg* data types, and document this restriction. =A0I >> probably could allow regtype because that pg_type is preserved. > > I have applied the attached patch to CVS HEAD and 9.0 that prevent > migration when any reg* data type is used in a user table (except > regtype because pg_type.oid is preserved). This is a good change; however, there is still some potential for lossage here. What if the column were declared as type OID? Then it would be hard to tell whether migration was safe or not. Perhaps the right long-term solution is to try harder to preserve OIDs in more cases. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas wrote: > On Sat, Jul 24, 2010 at 11:37 PM, Bruce Momjian <bruce@momjian.us> wrote: > >> I am inclined to prevent pg_upgrade from migrating any database that > >> uses any of these reg* data types, and document this restriction. ?I > >> probably could allow regtype because that pg_type is preserved. > > > > I have applied the attached patch to CVS HEAD and 9.0 that prevent > > migration when any reg* data type is used in a user table (except > > regtype because pg_type.oid is preserved). > > This is a good change; however, there is still some potential for > lossage here. What if the column were declared as type OID? Then it > would be hard to tell whether migration was safe or not. Perhaps the > right long-term solution is to try harder to preserve OIDs in more > cases. You are right that an oid column cannot be tracked easily. It could refer to a user table with oids, or it might be a system row reference. I have considered preserving more oids, but that is going to increase the backend changes for pg_upgrade, and I am hesistant to do that until there is a claarer demand. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Excerpts from depstein's message of lun jul 26 08:05:24 -0400 2010: > I just want to note that one reason regclass may be used in user tables (as opposed to, say, regtype) is that in PL/pgSQLtrigger procedures there is a special variable TG_RELID, which provides a convenient reference to the table thatpulled the trigger (this is the case for some of our uses). I've wanted to use regclass (and regproc too, for that matter) in some db designs, but I've refrained precisely because of the movability issues. Note that you can use TG_SCHEMANAME and TG_RELNAME in plpgsql triggers anyway.
Alvaro Herrera wrote: > Excerpts from depstein's message of lun jul 26 08:05:24 -0400 2010: > > > I just want to note that one reason regclass may be used in user tables (as opposed to, say, regtype) is that in PL/pgSQLtrigger procedures there is a special variable TG_RELID, which provides a convenient reference to the table thatpulled the trigger (this is the case for some of our uses). > > I've wanted to use regclass (and regproc too, for that matter) in some > db designs, but I've refrained precisely because of the movability > issues. Were you worried about pg_upgrade movability issues, or just general movability issues? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from depstein's message of lun jul 26 08:05:24 -0400 2010:
>> I just want to note that one reason regclass may be used in user tables (as opposed to, say, regtype) is that in
PL/pgSQLtrigger procedures there is a special variable TG_RELID, which provides a convenient reference to the table
thatpulled the trigger (this is the case for some of our uses).
> I've wanted to use regclass (and regproc too, for that matter) in some
> db designs, but I've refrained precisely because of the movability issues.
> Note that you can use TG_SCHEMANAME and TG_RELNAME in plpgsql triggers
> anyway.
How does TG_RELID lead to wanting to store regclass columns, exactly?
I've always supposed that was a legacy parameter rather than something
anyone would actually use.
regards, tom lane
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from depstein's message of lun jul 26 08:05:24 -0400 2010:
> >> I just want to note that one reason regclass may be used in user
> tables (as opposed to, say, regtype) is that in PL/pgSQL trigger
> procedures there is a special variable TG_RELID, which provides a
> convenient reference to the table that pulled the trigger (this is the
> case for some of our uses).
>=20
> > I've wanted to use regclass (and regproc too, for that matter) in
> some
> > db designs, but I've refrained precisely because of the movability
> issues.
>=20
> > Note that you can use TG_SCHEMANAME and TG_RELNAME in plpgsql
> triggers
> > anyway.
>=20
> How does TG_RELID lead to wanting to store regclass columns, exactly?
> I've always supposed that was a legacy parameter rather than something
> anyone would actually use.
Here is one use case from our database. We store data for financial instrum=
ents. The data has a hierarchical structure. For instance, we have the foll=
owing tables:
asset
asset_option
asset_option_american
asset_option_european
Suppose we have a procedure that takes a list of asset ID's and performs di=
fferent actions depending on the type of asset. One way to implement this w=
ould be through table inheritance (which we do have). In order to find the =
subset of assets that are options, we could search the table a_asset_option=
, from which all option tables are derived. However, working with parent ta=
bles turns out to be very inefficient in some situations. Some queries, suc=
h as joins, result in the materialization of a huge aggregate table, follow=
ed by an inefficient scan of that table.
An alternative implementation is to have a set of registry tables, which pa=
rallel the asset tables. Thus, we have the following tables:
reg_asset
reg_asset_option
reg_asset_option_american
reg_asset_option_european
Each of these tables has two columns: one with an asset ID, the other is a =
regclass column that refers to an asset table. Triggers ensure that wheneve=
r a new asset is added to an asset table, its ID and table OID are added to=
the registry table for that type of asset, as well as to all registry tabl=
es that are higher in the hierarchy. (This is where TG_RELID comes into pla=
y.) Thus, an American option is registered in the tables reg_asset_option_a=
merican, reg_asset_option and reg_asset. If I wanted to know whether an ass=
et with a given ID is an option, I would only have to search the index of t=
he reg_asset_option table. I can also write dynamic queries, using the tabl=
e OID field of the registry tables as a proxy for the table name.
What makes the table OID an attractive choice for registry tables is that i=
t is just a single integer number, which takes up much less space and is mu=
ch faster when performing comparisons than the qualified table name. And th=
e reason regclass is a natural choice is that that is what trigger procedur=
es make available through the TG_RELID special variable.
Regards,
Dmitry