Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pzGK5-0004p1-7G for pgsql-general@arkaria.postgresql.org; Wed, 17 May 2023 12:32:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pzGK4-0007Ij-30 for pgsql-general@arkaria.postgresql.org; Wed, 17 May 2023 12:32:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pzGK3-0007Ia-Of for pgsql-general@lists.postgresql.org; Wed, 17 May 2023 12:32:03 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pzGK1-000J71-9w for pgsql-general@lists.postgresql.org; Wed, 17 May 2023 12:32: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 34HCVuED3097675; Wed, 17 May 2023 08:31:56 -0400 From: Tom Lane To: Avi Weinberg cc: "pgsql-general@lists.postgresql.org" Subject: Re: Postgres Incompatibility In-reply-to: References: Comments: In-reply-to Avi Weinberg message dated "Wed, 17 May 2023 06:05:52 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3097673.1684326716.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 17 May 2023 08:31:56 -0400 Message-ID: <3097674.1684326716@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Avi Weinberg writes: > I noticed this problem when creating a backup on Postgres 13 and restori= ng it on Postgres 15. > CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF pg_su= bscription > LANGUAGE sql > AS $$ > SELECT * from pg_subscription; > $$; > The Postgres backup creates the view syntax with " FROM sync.show_pg_sub= scription1() show_pg_subscription1(oid, subdbid, subname, subowner, suben= abled, subconninfo, subslotname, subsynccommit, subpublications);" Yeah. This is intentional: if we don't decompile function calls that way, then other cases break. I'd say the fundamental problem here is that you used the antipattern "SELECT * FROM ...", rather than listing out the columns you want explicitly. Even introductory SQL textbooks warn against doing that in long-lived code. It would actually still have worked if you'd not bothered with the intermediate function, which seems to be adding nothing except execution cost and complication. If you'd just done CREATE VIEW sync.pg_subscription_view1 AS SELECT * FROM pg_subscription; then this would have decompiled as selecting specific columns from pg_subscription, and it would have been quite safe against column additions or reordering in pg_subscription. Having said that, we do reserve the right to actually change existing columns in system catalogs. So you simply cannot expect that code that looks at system catalogs is never going to need to be touched when doing a major version upgrade. regards, tom lane