Re: BUG #6706: pg_upgrade fails when plpgsql dropped/re-created - Mailing list pgsql-bugs

On Mon, Jun 25, 2012 at 10:57:56PM +0000, m.sakrejda@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6706
> Logged by:          Maciek Sakrejda
> Email address:      m.sakrejda@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system:   Ubuntu 12.04 LTS  (3.2.0-25-generic x86_64)
> Description:
>
> Using the 9.2beta2 of pg_upgrade and for the upgrade-to cluster.
>
> Ran into an issue upgrading a 9.1 cluster via pg_upgrade. I can reproduce it
> consistently:
>
> 1. Create a 9.1 cluster
> 2. Run "drop extension plpgsql" as superuser
> 3. Run "create extension plpgsql" as non-superuser
> 4. Perform normal upgrade via pg_upgrade
>
> The last step fails and I get the following error in
> pg_upgrade_restore.log:
>
> SELECT binary_upgrade.create_empty_extension('plpgsql', 'pg_catalog', false,
> '1.0', NULL, NULL, ARRAY[]::pg_catalog.text[]);
> psql:pg_upgrade_dump_db.sql:40: ERROR:  duplicate key value violates unique
> constraint "pg_extension_name_index"
> DETAIL:  Key (extname)=(plpgsql) already exists.

I can easily recreate this failure, even doing the drop/create as
super-user.  Fortunately the cause is clearly outlined in the C comments
of pg_dump.c:

    if (!binary_upgrade)
    {
        /*
         * In a regular dump, we use IF NOT EXISTS so that there isn't a
         * problem if the extension already exists in the target database;
         * this is essential for installed-by-default extensions such as
         * plpgsql.
         *
         * In binary-upgrade mode, that doesn't work well, so instead we skip
         * built-in extensions based on their OIDs; see
         * selectDumpableExtension.
         */
        appendPQExpBuffer(q, "CREATE EXTENSION IF NOT EXISTS %s WITH SCHEMA %s;\n",
                          qextname, fmtId(extinfo->namespace));
    }
    else
    {
        int         i;
        int         n;

        appendPQExpBuffer(q, "-- For binary upgrade, create an empty extension and insert objects into it\n");
        appendPQExpBuffer(q,
                          "SELECT binary_upgrade.create_empty_extension(");

For non-binary-upgrade dumps, IF NOT EXISTS easily allows drop/create of
plpgsql to work.  In binary-upgrade mode, selectDumpableExtension()
dumps all extensions that have an oid greater than FirstNormalObjectId.
This is the only use of FirstNormalObjectId in the pg_dump code, and
obviously something that needs attention.  Other objects are skipped if
they exist in pg_catalog, but extensions are always in pg_catalog, so
that filter will not work.

I can't think of how to fix this.  Perhaps we need to query the
pg_extension table as of the SELECT function all.  A workaround is to
renumber the oid of the plpgsql pg_extension row to be less than
FirstNormalObjectId, but that is hardly user-friendly.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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