On Wednesday, May 8, 2024, David G. Johnston <
david.g.johnston@gmail.com> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Apr 15, 2024 at 8:15 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> 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.
> Tom's recent bug regarding alter extension reminded me that no has
> expressed an opinion on this one.
A quick test says that ALTER EXTENSION SET SCHEMA *does* update
pg_extension.extnamespace to the new schema.
Re-reading your original message, I'm of the opinion that pg_cron's
control files are probably broken. If they are changing from
relocatable to not, then they need to specify that property in
a version-specific control file not the main one. Maybe there's
something else that needs to happen in our code, but updating
extnamespace doesn't seem to be it. Also, I do see code that
purports to cope with a version-related update of the relocatable
flag --- whether we test that, I'm not sure, but there's not
something obviously missing.
Let me simplify this a bit. But the parameter "relocatable" has nothing to do with this, "schema" does.
===========================
> cat testext.control
comment = 'testing extension'
default_version = '1.0'
relocatable = false
trusted = false
> cat testext--1.1.control
schema=pg_control
postgres=# create extension testext;
CREATE EXTENSION
postgres=# \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 update to '1.1';
ALTER EXTENSION
postgres=# \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)
postgres=# alter extension testext set schema pg_catalog;
ERROR: extension "testext" does not support SET SCHEMA
================================
The update succeeds but the value for schema does not change ignoring the explicit schema now present in testext--1.1.control
The subsequent error during alter extension shows that the system is aware of the newly added schema setting.
Never mind this last bit, the error is indeed from relocatable being false. Same overall problem though, the extension is saying it’s defined schema is pg_catalog but the system chooses not to reflect that fact. This is probably the bug but one we likely need to live with. Modifying alter extension to accept the named schema and update the catalog at least gives the DBA an out from a situation we failed to prevent.
Though I can see the argument that if the extension doesn’t care about schema because everything is hard-coded, and there isn’t an option to say “null”, that pg_catalog is basically the next best choice.
David J.