From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | m(dot)sakrejda(at)gmail(dot)com, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6706: pg_upgrade fails when plpgsql dropped/re-created |
Date: | 2012-07-02 15:42:11 |
Message-ID: | 20120702154211.GA25966@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Sat, Jun 30, 2012 at 02:37:47PM -0400, Tom Lane wrote:
> > 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.
Agreed What surprised me is that pg_dumpall/restore brings them back to
their default state too, and I haven't seen any complaints. (I would
complain.)
> 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.
Agreed. The conditionally function call worked just fine, but all those
dependent helper functions made a simple solution impossible.
What I decided to do was just conditionally drop the extension, just
like we conditionally create the plpgsql extension in non-binary-upgrade
mode. We could have just said drop/recreate of plpgsql was unsupported,
but it bothered me that we had different error cases for binary and
non-binary upgrades, which seemed odd.
Do we want to keep the FirstNormalObjectId on the condtional
drop/recreate?
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachment | Content-Type | Size |
---|---|---|
pg_upgrade.diff | text/x-diff | 846 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-07-02 17:01:51 | Re: BUG #6706: pg_upgrade fails when plpgsql dropped/re-created |
Previous Message | Amit Kapila | 2012-07-02 10:46:31 | Re: BUG #6712: PostgreSQL 9.2 beta2: alter table drop constraint does not work on inherited master table |