Thread: BUG #18038: Aliases removed from view definitions

The following bug has been logged on the website:

Bug reference:      18038
Logged by:          Asier Lostalé
Email address:      asier.lostale@openbravo.com
PostgreSQL version: 16beta2
Operating system:   Ubuntu 22.04
Description:

When defining a view in 16beta2, the sql representing that view removes
aliases. In previous versions those aliases where kept; furthermore, aliases
were added even if not defined.

In our case this is problematic as in our application we export view
definitions to text files to later import them, we support different
PostgreSQL versions and we would need to keep stable SQL among different
version (export <-> import).

For example:

create table test (c text);
create view test_v as select c from test;
create view test_v2 as select t.c from test t;

In PostgreSQL 16beta2:

\d+ test_v
 ...
View definition:
 SELECT c
   FROM test;


\d+ test_v2 
...
View definition:
 SELECT c
   FROM test t;

In previous versions:

\d+ test_v
...
View definition:
 SELECT test.c
   FROM test;

\d+ test_v2
...
View definition:
 SELECT t.c
   FROM test t;

PG Bug reporting form <noreply@postgresql.org> writes:
> When defining a view in 16beta2, the sql representing that view removes
> aliases. In previous versions those aliases where kept; furthermore, aliases
> were added even if not defined.

This is not a bug: there is nothing incorrect about either display
of the view.

I believe the proximate cause of the change is that there's a
heuristic in the parsetree decompiler to not attach a table alias to
a Var's name when there is only one relation in the range table (and
thus, no possible ambiguity).  That heuristic has been there for
decades, but it didn't fire in these specific cases because CREATE
VIEW attached some extra hidden range table entries to perform
permissions checking on the view itself.  That doesn't happen anymore
(the permissions checking is handled in another way now), so presto
no redundant alias.

We could alter or remove that heuristic, but not without changing
the behavior in yet other cases, so I doubt that twiddling it
would fix your problem in general.

> In our case this is problematic as in our application we export view
> definitions to text files to later import them, we support different
> PostgreSQL versions and we would need to keep stable SQL among different
> version (export <-> import).

I'm afraid your application has been built on sand.  We change
insignificant details of view/rule decompiling all the time,
and are not likely to stop.  I might have more sympathy for
your request if it didn't imply putting ruleutils.c into an
impossible straitjacket for all time to come.

            regards, tom lane

Nevertheless I understand it is very fragile. Is there any way to get the original sql text used to create the view?

On Fri, 2023-07-28 at 07:33 +0200, Asier Lostalé wrote:
> Is there any way to get the original sql text used to create the view?

PostgreSQL does not retain that information.

Perhaps you could use comments:

  COMMENT ON VIEW whatever IS 'source code';

Yours,
Laurenz Albe