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 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.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
