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