MIME-Version: 1.0
References: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com>
 <643857.1772122240@sss.pgh.pa.us> <717830.1772130430@sss.pgh.pa.us> <CAMbWs4_FLicDsNpotM0uEFkeveJ+gULe4VP0PyJ=FVsD9iYCGA@mail.gmail.com>
In-Reply-To: <CAMbWs4_FLicDsNpotM0uEFkeveJ+gULe4VP0PyJ=FVsD9iYCGA@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Fri, 27 Feb 2026 17:13:52 +0900
Message-ID: <CAMbWs49AUBVtMG=BXwfB5xdg1Zd9dwJg42C1hLayYtO2Kmxqpw@mail.gmail.com>
Subject: Re: pg_get_viewdef() produces non-round-trippable SQL for views with
 USING join on mismatched integer types
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Swirl Smog Dowry <swirl-smog-dowry@duck.com>, pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs49AUBVtMG%3DBXwfB5xdg1Zd9dwJg42C1hLayYtO2Kmxqpw%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 27, 2026 at 12:23=E2=80=AFPM Richard Guo <guofenglinux@gmail.co=
m> wrote:
> On Fri, Feb 27, 2026 at 3:27=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrot=
e:
> > The problem is obvious after looking at parseCheckAggregates: the
> > RTE_GROUP RTE is manufactured using the groupClauses list after we
> > have flattened that, which we are only doing for comparison purposes;
> > it shouldn't affect what goes into the parse tree.  I experimented
> > with just changing the order of operations, and that seems to fix it.

> Right.  We should keep the unmodified GROUP BY expressions in the
> parse tree, and then rely on the planner to flatten the join alias
> vars within them.
>
> +1 to the fix.

I am on the fence about whether this fix is safe to back-patch to v18.
I cautiously think it is safe, as it does not change the parsetree's
external representation and does not require an initdb.

However, this fix will only apply to newly created views.  Users who
have existing views affected by this bug will have to recreate them
after upgrading to get the corrected pg_get_viewdef output.  (really
kicking myself for this.)

- Richard