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

On Mon, Jul 02, 2012 at 01:01:51PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > +
> > + /*
> > + * 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);
>
> This doesn't seem like anything but a wart :-(. It's unlike the
> behavior for every other kind of object, it introduces the inconsistent

Well, our use of FirstNormalObjectId by pg_dump for extensions is unique
too.

> behavior even in non-binary-upgrade cases, and it does nothing at all to

This code is in the binary-upgrade block --- not sure how it could
affect non-binary upgrades.

This is the trimmed-down code block:

if (!binary_upgrade)
{
appendPQExpBuffer(q, "CREATE EXTENSION IF NOT EXISTS %s WITH SCHEMA %s;\n",
qextname, fmtId(extinfo->namespace));
}
else
{
--> appendPQExpBuffer(q, "DROP EXTENSION IF EXISTS %s;\n", qextname);
appendPQExpBuffer(q,
"SELECT binary_upgrade.create_empty_extension(");

The idea is that the IF NOT EXISTS and IF EXISTS are symmetric, which is
my goal.

> address the points I made about reproducing the previous state in cases
> where the admin removed the language or changed its permissions.

Well, it still does the create extension in binary mode like before ---
not sure what the problem is.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +