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

On Sat, Dec  1, 2012 at 10:25:10AM -0500, Bruce Momjian wrote:
> On Sat, Dec  1, 2012 at 07:43:17AM -0500, Andrew Dunstan wrote:
> > 
> > On 11/30/2012 11:10 PM, Tom Lane wrote:
> > >Some of the buildfarm members are failing the pg_upgrade regression test
> > >since commit 12ee6ec71f8754ff3573711032b9b4d5a764ba84.  I can duplicate
> > >it here, and the symptom is:
> > >
> > >pg_restore: creating TYPE float8range
> > >pg_restore: creating TYPE insenum
> > >pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > >pg_restore: [archiver (db)] Error from TOC entry 978; 1247 16584 TYPE insenum tgl
> > >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);
> > >
> > >I have not investigated why it apparently passes some places; this looks
> > >to me like a guaranteed failure.
> 
> I see now.  Sorry.  I was so focused on performance testing and never
> thought this cause pg_upgrade to fail.  I did not run my full tests this
> time.
> 
> It seems the problem is that we bundling the pg_upgrade oid set function
> into the same code block as ALTER TYPE, to preserve the type oid.  Let
> me see how to fix this.
> 
> Should I do something temporarily to get the buildfarm green again?
> Just revert the entire thing?

OK, I found the problem, and it isn't good.  Our manual clearly says:
ALTER TYPE ... ADD VALUE (the form that adds a new valueto 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
Commandwas:-- For binary upgrade, must preserve pg_type oidSELECT
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.512000      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.9416000    223.67  164.27  187.40  159.5332000     543.93  324.63  366.44  317.9364000    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.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +