Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4pnZ-001KOv-R6 for pgsql-bugs@arkaria.postgresql.org; Wed, 08 May 2024 22:30:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s4pnX-003DkU-J7 for pgsql-bugs@arkaria.postgresql.org; Wed, 08 May 2024 22:30:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4pnX-003DkM-AP for pgsql-bugs@lists.postgresql.org; Wed, 08 May 2024 22:30:03 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4pnV-00054u-1S for pgsql-bugs@lists.postgresql.org; Wed, 08 May 2024 22:30:02 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 448MTxXa935497; Wed, 8 May 2024 18:29:59 -0400 From: Tom Lane To: "David G. Johnston" cc: PostgreSQL Bug List Subject: Re: Upgrading an extension's extnamespace from user-specified to a defined schema breaks dump/restore In-reply-to: References: Comments: In-reply-to "David G. Johnston" message dated "Wed, 08 May 2024 15:01:37 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <935495.1715207399.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Wed, 08 May 2024 18:29:59 -0400 Message-ID: <935496.1715207399@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" 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. regards, tom lane