public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Swirl Smog Dowry <[email protected]>
Cc: [email protected]
Cc: Richard Guo <[email protected]>
Subject: Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types
Date: Thu, 26 Feb 2026 11:10:40 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com>
References: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com>

Swirl Smog Dowry <[email protected]> writes:
> When a view uses a USING join on columns with different integer
> types (integer vs bigint) and the SELECT clause contains an explicit
> narrowing cast, pg_get_viewdef() produces SQL that PostgreSQL itself
> rejects. This makes pg_dump produce dumps that fail on restore for
> any such view.

Hmm, yeah.  This used to work as-expected, too.  "git bisect" finds
that it broke at

247dea89f7616fdf06b7272b74abafc29e8e5860 is the first bad commit
commit 247dea89f7616fdf06b7272b74abafc29e8e5860
Author: Richard Guo <[email protected]>
Date:   Tue Sep 10 12:35:34 2024 +0900

    Introduce an RTE for the grouping step

Looking at the parse tree for the problem query, I see

	      {RANGETBLENTRY 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname *GROUP* 
	         :colnames ("?column?" "label")
	         }
	      :rtekind 9 
	      :groupexprs (
	         {FUNCEXPR 
	         :funcid 481 
	         :funcresulttype 20 
	         :funcretset false 
	         :funcvariadic false 
	         :funcformat 2 
	         :funccollid 0 
	         :inputcollid 0 
	         :args (
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 23 
	            :vartypmod -1 
	            :varcollid 0 
	            :varnullingrels (b)
	            :varlevelsup 0 
	            :varreturningtype 0 
	            :varnosyn 1 
	            :varattnosyn 1 
	            :location -1
	            }
	         )
	         :location -1
	         }
	         {VAR 
	         :varno 2 
	         :varattno 2 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varnullingrels (b 3)
	         :varlevelsup 0 
	         :varreturningtype 0 
	         :varnosyn 2 
	         :varattnosyn 2 
	         :location 32
	         }
	      )
	      :lateral false 
	      :inFromCl false 
	      :securityQuals <>
	      }

The first groupexpr is the same as the joinaliasvars entry for that
column in the JOIN RTE.  This surprises me: I'd expect to see a
reference to the join output column there, ie Var 3/1, because I'm
pretty sure that's what parsing of "GROUP BY year" would have produced
initially.  If it were like that, I think ruleutils would produce the
desired output.  So I'd tentatively classify this as "join alias Vars
are being flattened too soon".  Richard, any thoughts?

			regards, tom lane

reply

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: pg_get_viewdef() produces non-round-trippable SQL for views with USING join on mismatched integer types
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox