pg_upgrade from 8.3.4 issue

On Tue, Aug 30, 2011 at 12:48 PM, Justin Arnold <jharnold81@gmail.com> wrote:

Hey, I am trying to upgrade a CentOS 5.4 32bit test server running postgres
8.3.4 to postgres 9.1 RC1 and am running into an error I haven't seen
mentioned in the forums (at least dealing with the upgrade process). The
steps I ran through for the upgrade are...

Stop postgres
move /usr/local/pgsql to /usr/local/pgsql.8.3
move /usr/pgdata/data to /usr/pgdata/data.8.3
build 9.1 RC1 from source using "./configure --with-perl --with-openssl
--disable-integer-datetimes; make; make install"
build and install pg_upgrade and pg_upgrade_support
swap to postgres user
run "/usr/local/pgsql/bin/initdb --lc-collate=C --lc-ctype=C
--lc-messages=C --lc-monetary=C --lc-numeric=C --lc-time=C -E SQL-ASCII -D
/usr/pgdata/data" to create the 9.1 cluster and set the settings to match
the old cluster
/usr/local/pgsql/bin/pg_upgrade --link --old-datadir /usr/pgdata/data.8.3/
--new-datadir /usr/pgdata/data/ --old-bindir /usr/local/pgsql.8.3/bin/
--new-bindir /usr/local/pgsql/bin/

What I get is...
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          DB command
failed
SELECT * FROM pg_catalog.pg_prepared_xact()
ERROR:  a column definition list is required for functions returning
"record"
Failure, exiting
The binaries for the 8.3.4 install were built from source using
the --with-perl --with-openssl options as well. Any thoughts on what I might
be able to do to fix or workaround this? Thanks!
- Justin

It looks like some time after 8.3 was released that function was
changed from returning 'record'. This is making me wonder if the
upgrade process was ever tested/verified on 8.3. I absolutely do not
advise doing this without taking a lot of precautions, but you might
force your way past that step with:

[login as superuser]
alter function pg_prepared_xact() rename to hack;
create function pg_catalog.pg_prepared_xact(
OUT transaction xid,
OUT gid text,
OUT prepared timestamptz,
OUT ownerid oid, OUT dbid oid) returns setof record as
$$
select * from hack() r(transaction xid, gid text, prepared
timestamptz, ownerid oid, dbid oid);
$$ language sql;

I'd like to see someone more comfortable with the upgrade process
comment before attempting that though. Another way to do that is to
hack the r(transaction xid, gid text, prepared timestamptz,
ownerid oid, dbid oid);
at the end of whatever query is trying to the select.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

It looks like some time after 8.3 was released that function was
changed from returning 'record'. This is making me wonder if the
upgrade process was ever tested/verified on 8.3.

Not lately, apparently :-(

I absolutely do not
advise doing this without taking a lot of precautions, but you might
force your way past that step with:

I think it'd be a lot safer to modify (or just remove) the test in
pg_upgrade. It looks like a one-liner:

prep_status("Checking for prepared transactions");

res = executeQueryOrDie(conn,
"SELECT * "
"FROM pg_catalog.pg_prepared_xact()");

if (PQntuples(res) != 0)
pg_log(PG_FATAL, "The %s cluster contains prepared transactions\n",
CLUSTER_NAME(cluster));

There's no reason at all for this code to not use the published API,
which is the pg_prepared_xacts system view.

regards, tom lane

Thanks Tom and Merlin, I removed that logic from check.c, rebuilt, and it
worked fine.

On Tue, Aug 30, 2011 at 2:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

I think it'd be a lot safer to modify (or just remove) the test in
pg_upgrade. It looks like a one-liner:

Specifically, the attached patch takes care of the problem. Thanks
for reporting it!

regards, tom lane

diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
index 9690695..93b9e69 100644
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_for_prepared_transactions(ClusterI
*** 532,538 ****

res = executeQueryOrDie(conn,
"SELECT * "
! "FROM pg_catalog.pg_prepared_xact()");

  	if (PQntuples(res) != 0)
  		pg_log(PG_FATAL, "The %s cluster contains prepared transactions\n",
--- 532,538 ----

res = executeQueryOrDie(conn,
"SELECT * "
! "FROM pg_catalog.pg_prepared_xacts");

if (PQntuples(res) != 0)
pg_log(PG_FATAL, "The %s cluster contains prepared transactions\n",

FYI, also, yesterday, I fixed a pg_upgrade bug when upgrading from 8.3 
--- I suggest you wait for 9.0.5 or pull git head for the release you
want.

---------------------------------------------------------------------------

Tom Lane wrote:

I wrote:

I think it'd be a lot safer to modify (or just remove) the test in
pg_upgrade. It looks like a one-liner:

Specifically, the attached patch takes care of the problem. Thanks
for reporting it!

regards, tom lane

diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
index 9690695..93b9e69 100644
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_for_prepared_transactions(ClusterI
*** 532,538 ****

res = executeQueryOrDie(conn,
"SELECT * "
! "FROM pg_catalog.pg_prepared_xact()");

if (PQntuples(res) != 0)
pg_log(PG_FATAL, "The %s cluster contains prepared transactions\n",
--- 532,538 ----

res = executeQueryOrDie(conn,
"SELECT * "
! "FROM pg_catalog.pg_prepared_xacts");

if (PQntuples(res) != 0)
pg_log(PG_FATAL, "The %s cluster contains prepared transactions\n",

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

+ It's impossible for everything to be true. +