Re: Obtaining a more consistent view definition when a UNION subquery contains undecorated constants

From: Jacob Champion <pchampion(at)pivotal(dot)io>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jimmy Yih <jyih(at)pivotal(dot)io>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>, Jim Doty <jdoty(at)pivotal(dot)io>
Subject: Re: Obtaining a more consistent view definition when a UNION subquery contains undecorated constants
Date: 2018-10-02 22:24:00
Message-ID: CABAq_6GQQ-hs5Lfaa2Tg1JR3D7UgBpwbTJOsEfRE2ow_Qwxvdw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Sep 27, 2018 at 3:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jimmy Yih <jyih(at)pivotal(dot)io> writes:
> > Looking at the internal code (mostly get_from_clause_item() function), we
> > saw that when a subquery is used, there is no tuple descriptor passed to
> > get_query_def() function. Because of this, get_target_list() uses the
> > resnames obtained from the pg_rewrite entry's ev_action field. However, it
> > seems to be fairly simple to construct a dummy tuple descriptor from the
> > ev_action to pass down the call stack so that the column names will be
> > consistent when deparsing both T_A_Const and T_TypeCast parse tree nodes
> > involving a UNION. Attached is a patch that demonstrates this.
>
> I'm afraid that this just moves the weird cases somewhere else, ie
> you might see an AS clause where you had none before, or a different
> AS clause from what you originally wrote. The parser has emitted the
> same parse tree as if there were an explicit AS there; I doubt that
> we want ruleutils to second-guess that unless it really has to.

Can you give a quick example of something that "breaks" with this
approach? I think we're having trouble seeing it.

It might help to have some additional context on why we care: this
problem shows up in pg_upgrade testing, since we're basically
performing the following steps:
- create a view in the old database
- dump the old database schema
- load the schema into the new database
- dump the new database schema and compare to the old one

So from this perspective, we don't mind so much if the view definition
changes between creation and dump, but we do mind if it changes a
second time after the dump has been restored, since it shows up as a
false negative in the diff. In other words, we'd like the dumped view
definitions to be "stable" with respect to dumps and restores.

Thanks,
--Jacob

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2018-10-02 22:50:36 Re: Progress reporting for pg_verify_checksums
Previous Message Tom Lane 2018-10-02 22:04:33 Re: Performance improvements for src/port/snprintf.c