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"

-- Another oddity
-- relocatable: true
\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.