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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +