Re: --single-transaction hack to pg_upgrade does not work - Mailing list pgsql-hackers
From | Bruce Momjian |
---|---|
Subject | Re: --single-transaction hack to pg_upgrade does not work |
Date | |
Msg-id | 20121201154106.GJ27120@momjian.us Whole thread Raw |
In response to | Re: --single-transaction hack to pg_upgrade does not work (Bruce Momjian <bruce@momjian.us>) |
Responses |
Re: --single-transaction hack to pg_upgrade does not work
|
List | pgsql-hackers |
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. +
pgsql-hackers by date: