Re: [BUGS] pg_upgrade issues
Alvaro Herrera alvhe...@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). 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. Here is one use case from our database. We store data for financial instruments. The data has a hierarchical structure. For instance, we have the following 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 different actions depending on the type of asset. One way to implement this would 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 tables turns out to be very inefficient in some situations. Some queries, such as joins, result in the materialization of a huge aggregate table, followed by an inefficient scan of that table. An alternative implementation is to have a set of registry tables, which parallel 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 whenever 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 tables that are higher in the hierarchy. (This is where TG_RELID comes into play.) Thus, an American option is registered in the tables reg_asset_option_american, reg_asset_option and reg_asset. If I wanted to know whether an asset with a given ID is an option, I would only have to search the index of the reg_asset_option table. I can also write dynamic queries, using the table 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 it is just a single integer number, which takes up much less space and is much faster when performing comparisons than the qualified table name. And the reason regclass is a natural choice is that that is what trigger procedures make available through the TG_RELID special variable. Regards, Dmitry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Alvaro Herrera alvhe...@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). 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Bruce Momjian wrote: depst...@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). Dmitry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote: Bruce Momjian wrote: depst...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
On Sat, Jul 24, 2010 at 11:37 PM, Bruce Momjian br...@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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Robert Haas wrote: On Sat, Jul 24, 2010 at 11:37 PM, Bruce Momjian br...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
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). 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
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/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). 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Bruce Momjian wrote:
depst...@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 br...@momjian.ushttp://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 - 1.12
--- contrib/pg_upgrade/check.c 25 Jul 2010 03:19:48 -
***
*** 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 !=
Re: [BUGS] pg_upgrade issues
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 br...@momjian.ushttp://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 - 1.13
--- contrib/pg_upgrade/check.c 25 Jul 2010 03:43:07 -
***
*** 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:
*
Re: [BUGS] pg_upgrade issues
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_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. 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. Thanks, Dmitry
Re: [BUGS] pg_upgrade issues
depst...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
depst...@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 br...@momjian.ushttp://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 - 1.13
--- contrib/pg_upgrade/file.c 9 Jul 2010 16:41:46 -
***
*** 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;
}
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
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 br...@momjian.us depst...@alliedtesting.com wrote: BB 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=20 start pg_upgrade.log 21 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote: BB 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=20 start pg_upgrade.log 21 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Another issue: 4. The --link option doesn't seem to work on Windows: pg_upgrade still copies data from the old cluster to the new. There doesn't appear to be a way to upgrade a database on Windows without copying the entire uncompressed database, which can be a problem where disk space is limited. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
