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

On 2012-12-01 10:55:09 -0500, Bruce Momjian wrote:
> 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.
> >
> > 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?

Could we possibly allow adding enum values to a type which was just created in
this transaction? That shouldn't be too hard. At least easier than providing
the capability to pre-assign the next N oids...

Greetings,

Andres Freund

--Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services