Re: pg_upgrade from 8.3.4 issue

On Tue, Aug 30, 2011 at 12:48 PM, Justin Arnold <jharnold81(at)gmail(dot)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