Re: BUG #6706: pg_upgrade fails when plpgsql dropped/re-created

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Sat, Jun 30, 2012 at 11:12:56AM -0400, Tom Lane wrote:
>> Did it restore the nonstandard ownership of the schema?

> No --- drop/create of the public schema produces:
> ...
> However, surprisingly, a simple pg_dump/restore also does not preserve
> the public schema permissions either. :-(

Right. My point is that there is a whole lot of stuff that initdb
creates but does not mark "pinned" in pg_depend, with the intention that
users could drop it, and perhaps recreate similarly-named objects with
different properties. We have never had a very sane story for what
would happen to such modified objects during dump/reload, and pg_upgrade
is no better (or worse). I don't think there's too much point in
thinking about plpgsql alone without also worrying about

* system views (including the information schema)
* collations
* conversions
* text search dictionaries

Now for a lot of this stuff, it's perhaps reasonable that a major
version upgrade would restore the objects to standard state. I'm
thinking though that it's rather bad that we treat either the public
schema or the plpgsql language that way. In particular, an admin
might have wished to remove or restrict those two objects for security
reasons, in which case he'd not be very happy if pg_upgrade resurrected
them or restored their default permissions.

BTW, I think your proposed fix doesn't work even without considering
this angle --- it would prevent creation of the duplicate pg_extension
row, but the binary-upgrade dump script is still going to try to create
the extension's member objects.

regards, tom lane