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.

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. +

depstein@alliedtesting.com wrote:

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"

[ Email moved to hackers list.]

Ah, interesting. I did some research and it turns out there is no way to
send server output and pg_ctl output to the same file on Win32. I have
updated the pg_ctl docs to reflect this (first attached patch). I have
also developed a patch (second attached patch) that sends pg_ctl output
to 'nul' on win32 so we can store the server output in the log file.

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).

Thanks, fixed in second attached patch.

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 specified module could not be found.

I had to uninstall the debugger from the old database before I could
proceed.

Uh, pg_upgrade has to have the same dll's, so I suggest you install that
into the new server the same way you did in the old server. If it was
installed by the old installer, the new installer should have done the
same.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

depstein@alliedtesting.com wrote:

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.

[ Email moved to hackers list.]

I am confused why you are seeing this behavior. I know my Win32 tester
had it working. We have this code:

#ifdef WIN32
static int
win32_pghardlink(const char *src, const char *dst)
{
/*
* CreateHardLinkA returns zero for failure
* http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
*/
if (CreateHardLinkA(dst, src, NULL) == 0)
return -1;
else
return 0;
}
#endif

and we test for failures. We even have code that tests to make sure
hard links work before we start the migration. In fact, pg_upgrade
--check will perform the hard link test without performing the upgrade.

I assume you don't have the ability to try a patch that would exit just
before the hard link test removes its test file.

--
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>

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 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.

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. +

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

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. +

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. +

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).

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.  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

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/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.

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 <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/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