Upgrading an extension's extnamespace from user-specified to a defined schema breaks dump/restore

tl/dr - alter extension ... set schema ...  needs to update pg_extension.extnamespace if the named schema matches the current value in the control file.  Otherwise, extension authors can and have introduced a dump/restore failure mode that the DBA can only fix via direct catalog manipulation.

Context:

The pg_cron project has decided they want to change from allowing the DBA to specify the schema during create extension to instead forcing pg_extension.extnamespace to be linked to the pg_catalog schema (starting in their v1.5, by accident but subsequently accepted as policy).

Description:

Users of pg_cron that cross the boundary of that decision are required to drop the extension and recreate it because extension update does not inspect the control file in order to update the schema named therein.  For extension set schema it is an error to even specify set schema if there is a schema named in the control file - which requires relocate=false.  Combine those two behaviors with the fact that pg_dump will always attach a schema clause to the dumped create extension command and it becomes impossible to dump and restore because the metadata cannot be made to conform to a control file now containing schema = pg_catalog.

Resolution and Reasoning:

IMO we need to fix and back-patch letting set schema accept the user specifying the name of the schema found in the control file.  This at least lets the DBA make a judgement call on their database to alter the schema to what it would be if the extension were to be dropped and recreated.  If the name specified does not match the one in the control file then the existing error would remain since the control file has to be defined non-relocatable for schema to even be specified.  This seems even more like a bug oversight since we have already programmed create extension to behave in this very manner - allowing the specification of a schema even for an extension with a fixed control schema so long as they match.

It is arguable whether extension update is behaving sanely here but I imagine any fix here would not be back-patchable whereas fixing alter extension to go from error to useful behavior is a safe back-patchable bug fix.

 

David J.

P.S. The script I used here, where the extension creates a table with the same name, produces an oddity when relocation is allowed.  See the final stanza of the script below.

-- testext.control
comment = 'testing extension'
default_version = '1.0'
relocatable = false
trusted = false

-- testext--1.0.sql
CREATE TABLE public.testext(id serial primary key);

-- testext--1.0--1.1.sql
CREATE TABLE public.testext2(id serial primary key);

create extension testext;

\dx

--                 List of installed extensions
--  Name   | Version |   Schema   |         Description
-----------+---------+------------+------------------------------
-- plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
-- testext | 1.0     | public     | testing extension
--(2 rows)

alter extension testext set schema pg_catalog;
--ERROR:  testext is a table's row type
--HINT:  Use ALTER TABLE instead.

-------------------------------- change testext.control ------------------------------

modify: default_version = '1.1'

add: schema = pg_catalog

alter extension testext update TO "1.1";

\dx
--                 List of installed extensions
--   Name   | Version |   Schema   |         Description
-- ---------+---------+------------+------------------------------
--  plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
--  testext | 1.1     | public     | testing extension
-- (2 rows)

-- pg_dump ...
-- CREATE EXTENSION IF NOT EXISTS testext WITH SCHEMA public;

drop extension testext;
--DROP EXTENSION

CREATE EXTENSION IF NOT EXISTS testext WITH SCHEMA public;
--ERROR:  extension "testext" must be installed in schema "pg_catalog"

\dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 testext | 1.0     | public     | testing extension
(2 rows)

postgres=# alter extension testext set schema pg_catalog;
ERROR:  testext is a table's row type
HINT:  Use ALTER TABLE instead.