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

On Sat, Dec 1, 2012 at 10:55:09AM -0500, Bruce Momjian wrote:
> 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?

OK, Andrew has accurately told me via IM that ALTER TYPE ... ADD VALUE
is only emitted by pg_dump in binary-upgrade mode. Seems you can run it
manually, but pg_dump doesn't use it except for binary-upgrade mode, and
I now see that in the code.

So, that removes my concern about pg_restore --single-transaction in
general.

So, we have to decide if we should improve pg_dump to split up the
function call and ALTER TYPE ... ADD VALUE command, or fall back to text
dump mode and psql. That removes the optimization of using custom
format, and the optimization of using pg_restore. However, I don't see
how I can guarantee that the pg_upgrade oid setting function will be
called just _before_ the ALTER TYPE ... ADD VALUE command without having
them in the same command string package.

Shame --- pg_upgrade performance was improving so steadily, I was hoping
to see negative duration times soon. ;-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +