pg_upgrade ?deficiency - Mailing list pgsql-general

Hello all,

I am upgrading a 8.4 cluster to 9.1 and am seeing the following:

    SQL command failed

    CREATE TEMPORARY TABLE info_rels (reloid) AS
    SELECT c.oid
    FROM
        pg_catalog.pg_class c
            JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
                LEFT OUTER JOIN pg_catalog.pg_index i ON c.oid = i.indexrelid
    WHERE
        relkind IN ('r', 'm', 'i', 'S')
            AND
        i.indisvalid IS DISTINCT FROM false
            AND
        i.indisready IS DISTINCT FROM false
            AND
        ((n.nspname !~ '^pg_temp_'
            AND
          n.nspname !~ '^ pg_toast_temp_'
              AND
          n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast')
              AND
          c.oid >= 16384
        )
            OR
        (n.nspname = 'pg_catalog'
            AND
         relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index')
        ));

    ERROR:  transaction is read-only

Now, this is quite understandable since one of the databases
is set to

    ALTER DATABASE ... SET DEFAULT_TRANSACTION_READ_ONLY TO ON;

However, since the above setting is something which can
be expected every so often in any odd PostgreSQL cluster
(and not some weird coincidence no one really knows how
they got into in the first place) I would think pg_upgrade
really should be able to handle.

Technically that's pretty easy - make sure transactions are
set to readwrite for the pg_upgrade run by any number of
means:

    - ALTER DATABASE before/after pg_upgrade
    - ALTER USER running the pg_upgrade
    - SET TRANSACTION READ WRITE at the appropriate times
    - ...

Or at least this limitation of pg_upgrade (requiring
DB write access) should get a mention in the docs and/or
man page.

What is the informed opinion on this ?

Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346