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

On Mon, Jun 25, 2012 at 10:57:56PM +0000, m(dot)sakrejda(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6706
> Logged by: Maciek Sakrejda
> Email address: m(dot)sakrejda(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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