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

On Wednesday, May 8, 2024, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wed, May 8, 2024 at 3:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> > On Mon, Apr 15, 2024 at 8:15 PM David G. Johnston <
>> > david(dot)g(dot)johnston(at)gmail(dot)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.