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

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

In response to

Responses

Browse pgsql-hackers by date

  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