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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c new file mode 100644 index afb28a8..a4ead5d *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** dumpExtension(Archive *fout, ExtensionIn *** 7331,7336 **** --- 7331,7344 ---- int n; appendPQExpBuffer(q, "-- For binary upgrade, create an empty extension and insert objects into it\n"); + + /* + * We unconditionally create the extension, so we must drop it if it + * exists. This could happen if the user deleted 'plpgsql' and then + * readded it, causing its oid to be greater than FirstNormalObjectId. + */ + appendPQExpBuffer(q, "DROP EXTENSION IF EXISTS %s;\n", qextname); + appendPQExpBuffer(q, "SELECT binary_upgrade.create_empty_extension("); appendStringLiteralAH(q, extinfo->dobj.name, fout);
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs