From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Subject: Re: Upgrading an extension's extnamespace from user-specified to a
 defined schema breaks dump/restore
In-reply-to: 
 <CAKFQuwayeW5=9rDHrSkSDbR6SW6sEpzpRxcVz64iKKwra4ysyA@mail.gmail.com>
References: 
 <CAKFQuwa1cb9y6OW9rSmXFGH69J3ZEQOm3+Od4Ang4qi74YHcTw@mail.gmail.com>
 <CAKFQuwYbqurFWg0W8mqBsHLsCyxZAZqBdV0q-beEFTbeBNhO2A@mail.gmail.com>
 <935496.1715207399@sss.pgh.pa.us>
 <CAKFQuwayeW5=9rDHrSkSDbR6SW6sEpzpRxcVz64iKKwra4ysyA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 08 May 2024 16:12:47 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <950006.1715212519.1@sss.pgh.pa.us>
Date: Wed, 08 May 2024 19:55:19 -0400
Message-ID: <950007.1715212519@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/950007.1715212519%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.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.

I'm also not really inclined to assume that even if we let
SET SCHEMA happen, it would successfully move such an extension.
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.

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.  But I don't want to take responsibility for
making the member objects match that.

			regards, tom lane