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

On Wed, May 8, 2024 at 4:55 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:
> > 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
>
> So the actual situation with pg_cron is that they've *never* been
> relocatable, but now they want to bind a particular schema where
> before they let the user choose?
>
> I absolutely won't accept a suggestion that we should allow ALTER
> SET SCHEMA if the extension is saying it's not relocatable.
> Potentially breaking other non-relocatable extensions is not
> an okay tradeoff for coping with an extremely questionable
> decision on pg_cron's part.
>

This doesn't work unless schema=something is in the control file in which
case that very fact says the extension is expecting the alter command to
work and make the catalog value equal the control file value if it isn't
already. Plus, the informed DBA has to perform the manual action here.

>
> I'm also not really inclined to assume that even if we let
> SET SCHEMA happen, it would successfully move such an extension.
>

It wouldn't even try.

> I think a reasonable penalty on pg_cron for this is that they
> should have to supply an extension upgrade script that does
> per-object ALTER XXX SET SCHEMA to get everything (or at least
> as much as they want) into the newly selected schema.
>

Immaterial, they ignore @extschema@ already.

> I do see a missing bit, which is that if the new control
> file says schema = whatever, we should probably update
> extnamespace to match --- which I guess was your point
> as well.

I suppose it partly depends on how strictly one interprets:

"An extension might be relocatable during installation but not afterwards."

https://www.postgresql.org/docs/current/extend-extensions.html

If "installation" includes version updates then yes we should be changing
the value on-the-fly and the extension needs to determine what that means
for them.

Though that seems like an impossible definition to apply without having the
update scripts see both the existing name and the new name. And so if it
is just creation-time only then the current behavior is technically correct.

But I don't want to take responsibility for
> making the member objects match that.
>
>
Right now an extension that was erroneously configured as "relocatable
during installation" when in fact it "does not support relocation at all"
has no way besides drop-and-create to get into the correct configuration
internally and the attempt to do so, which we silently accept but ignore,
causes dump and restore to fail since the schema name present in the
catalogs is part of the dump and it will not match the control file,
causing create extension to fail.

A relocatable extension is unaffected here since it cannot specify a schema
name in the control file.

For either version of non-relocatable extension, alter extension is not
expected to relocate it. But it is still useful to update the metadata if
the DBA asks us to do so explicitly, likely only at the behest of the
extension author who knows that it is safe to do so for their extension.

I would leave the update path alone, leaving whatever the installation-time
extension was in place.

I would give the DBA a tool to unbreak their already broken system. I'm
not seeing how they can meaningfully break it more.

We thus retain the general promise that our system doesn't understand how
to change from "relocate during install" to "doesn't support relocate at
all" - it will not just magically do stuff if you later add a
schema=something clause to your control file, knowing full well that the
schema just added likely isn't the one in the existing user's system. All
that, while still giving the DBA user an out via alter extension that,
instead of failing, does the minimal useful thing and updates the catalog
with the control file schema, and assuming that, just like with any other
"does not support relocation at all" extension, everything is managed
without either caring what @extschema@ is or assuming it is the value in
the control file.

David J.

David J.