From: | Robert Treat <rob(at)xzilla(dot)net> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Florian Pflug <fgp(at)phlo(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE |
Date: | 2011-02-28 04:38:10 |
Message-ID: | AANLkTin5tJZiGE=5ZRcHjWdVRq_oN4YhDCUSnfMgSNOe@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Jan 6, 2011 at 10:08 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Tom Lane wrote:
>> Bruce Momjian <bruce(at)momjian(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-02-28 05:21:06 | Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE |
Previous Message | Andrew Dunstan | 2011-02-28 03:20:06 | Re: Native XML |