Re: --single-transaction hack to pg_upgrade does not work

On Sat, Dec 1, 2012 at 10:41:06AM -0500, Bruce Momjian wrote:
> OK, I found the problem, and it isn't good. Our manual clearly says:
>
> ALTER TYPE ... ADD VALUE (the form that adds a new value
> to an enum type) cannot be executed inside a transaction block.
>
> This also means it can't be passed inside an implicit transaction block,
> which happens when you pass:
>
> SELECT 1; SELECT 2;
>
> as a string, and I think this is what pg_restore is doing. So, not only
> is --single-transction causing the failure, but even without
> --single-transction, pg_restore just passes the multi-statement string
> to the backend, and you get the error:
>
> pg_restore: [archiver (db)] could not execute query: ERROR: ALTER TYPE
> ... ADD cannot run inside a transaction block
> Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT binary_upgrade.set_next_pg_type_oid('16584'::pg_catalog.oid);
>
> psql dutifully splits up the string into separate commands, which is why
> the previous pg_dumpall | psql coding worked. One simple fix would be
> to revert to plain output format, and return to using psql. Of course,
> we lose a lot of performance with that. The pending AtOEXAct patch gets
> us most of the performance back:
>
> #tbls git -1 AtOEXAct both
> 1 11.06 13.06 10.99 13.20
> 1000 21.71 22.92 22.20 22.51
> 2000 32.86 31.09 32.51 31.62
> 4000 55.22 49.96 52.50 49.99
> 8000 105.34 82.10 95.32 82.94
> 16000 223.67 164.27 187.40 159.53
> 32000 543.93 324.63 366.44 317.93
> 64000 1697.14 791.82 767.32 752.57
>
> so maybe that's how we have to go, or modify pg_dump to emit the
> binary-upgrade function call as a separate pg_dump entry, rather than
> lumping it in with ALTER TYPE ... ADD VALUE.

Scratch that idea. By definition, no matter how we modify pg_dump or
pg_restore, ALTER TYPE ... ADD VALUE is never going to be able to be run
in a multi-statement transaction, so we have to certainly remove
--single-transction, and then we can decide if we want to continue using
pg_restore with an improved pg_dump, or just fall back to pg_dump and
psql.

I am thinking at this point I should just switch to pg_dump text format
and psql to get the build farm green again, but not lose the other
changes that give us per-database dumps.

This does make me wonder why pg_restore supports --single-transaction if
it has known failure cases (that are not documented in the pg_restore
manual page, only in the ALTER TYPE manual page). Are users really
going to know if their database has objects that are not supported by
--single-transaction?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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