From: Tom Lane <tgl@sss.pgh.pa.us>
To: Avi Weinberg <AviW@gilat.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Postgres Incompatibility
Date: Wed, 17 May 2023 08:31:56 -0400
Message-ID: <3097674.1684326716@sss.pgh.pa.us> (raw)
In-Reply-To: <DB9PR07MB71804E6D1CA3449412DBA09FCB7E9@DB9PR07MB7180.eurprd07.prod.outlook.com>
References: <DB9PR07MB71800437881031B83D4384ACCB799@DB9PR07MB7180.eurprd07.prod.outlook.com>
<DB9PR07MB71804E6D1CA3449412DBA09FCB7E9@DB9PR07MB7180.eurprd07.prod.outlook.com>
Avi Weinberg <AviW@gilat.com> writes:
> I noticed this problem when creating a backup on Postgres 13 and restoring it on Postgres 15.
> CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF pg_subscription
> LANGUAGE sql
> AS $$
> SELECT * from pg_subscription;
> $$;
> The Postgres backup creates the view syntax with " FROM sync.show_pg_subscription1() show_pg_subscription1(oid, subdbid, subname, subowner, subenabled, 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, AviW@gilat.com
Subject: Re: Postgres Incompatibility
In-Reply-To: <3097674.1684326716@sss.pgh.pa.us>
References: <DB9PR07MB71800437881031B83D4384ACCB799@DB9PR07MB7180.eurprd07.prod.outlook.com> <DB9PR07MB71804E6D1CA3449412DBA09FCB7E9@DB9PR07MB7180.eurprd07.prod.outlook.com> <3097674.1684326716@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox