Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE

On Thu, Jan 6, 2011 at 10:08 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Tom Lane wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>> > We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade
>> > mode.  I am unclear if that might cause some other problems though.
>>
>> I finally figured out what was really bugging me about that proposal:
>> it's a one-shot hack for fixing one problem that could arise from
>> non-default ALTER DATABASE/ALTER ROLE settings.  Who's to say there
>> are not other such issues, either now or in the future?
>>
>> It occurs to me that a more principled way to deal with this class of
>> problems would be to delay restoring ALTER DATABASE/ALTER ROLE
>> settings until after everything else is done.  Not sure what the
>> implementation consequences of that would be.  Ideally we'd make
>> pg_dumpall output work that way in general, not just for pg_upgrade.
>
> Yep, it feels like a one-off that no one else will ever hit, and there
> are certainly other ALTER DATABASE SET commands that could also obstruct
> a restore.
>

Did anything ever come of this discussion? On one of the databases I
was upgrading, I ran into a similar problem with roles that are set as
roles. The problem seems to stem from pg_dumpall dumping roles in
alphabetical order:

CREATE ROLE asha;
ALTER ROLE asha SET role TO 'omniti';
.. sometime later ...
CREATE ROLE omniti;

It generates an error because the ALTER ROLE fails with the role not
existing, which causes pg_upgrade to bail out (it's in the on error
stop part).

ISTM this fails in general, so not blaming pg_upgrade; I think there
should probably be a fix in pg_dumpall to create all roles first
before running the alters, but there might be some other options.

Thoughts?


Robert Treat
play: xzilla.net
work: omniti.com
hiring: l42.org/Lg