Thread: pg_upgrade from 8.3.4 issue

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

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.

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