Re: Making view dump/restore safe at the column-alias level

On Fri, Dec 21, 2012 at 6:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> In commit 11e131854f8231a21613f834c40fe9d046926387 we rearranged
> ruleutils.c's handling of relation aliases to ensure that views can
> always be dumped and reloaded even in the face of confusing table
> renamings. I was reminded by
> http://archives.postgresql.org/pgsql-general/2012-12/msg00654.php
> that this is only half of the problem: you can still get burnt by
> ambiguous column references, and pretty easily at that.
>
> Aside from plain old ambiguity, there is a nastier problem: JOIN USING
> and NATURAL JOIN depend on particular column names matching up, which
> they might not do anymore after a column rename. We have discussed
> this previously (though I can't find the archives reference right now),
> and the best anybody came up with was to invent some syntax extension
> that would allow matching differently-named columns in USING, perhaps
> along the lines of USING (leftcol = rightcol, ...). But that's pretty
> ugly and nobody volunteered to actually do it.
>
> I had an idea though about how we might fix this without that. Assume
> that the problem is strictly ruleutils' to fix, ie we are not going to
> invent new syntax and we are not going to change the existing methods
> of assigning aliases to subselect columns. We clearly will need to let
> ruleutils assign new column aliases that are unique within each RTE
> entry. I think though that we can fix the JOIN USING problem if we
> introduce an additional idea that alias choices can be forced top-down.
> So a JOIN USING RTE would force the two columns being merged to be given
> the same alias already assigned to the merged column in the JOIN RTE.
> (If we ever get around to implementing the CORRESPONDING clause in
> UNION/INTERSECT/EXCEPT, it would have to do something similar.) We'd
> similarly force the output aliases at the top level of a view to be the
> view's known result column names (which presumably are distinct thanks
> to pg_attribute's unique constraint). Otherwise, as we descend the
> query tree, we can assign distinct column aliases to each column of an
> RTE, preferring the original name when possible but otherwise making it
> unique by adding a number, as we already did with the relation aliases.
>
> In the case of view-printing, once these aliases are all assigned we can
> represent them in the SQL output easily enough; that code is already
> there. I'm not sure whether it's a good idea for EXPLAIN to use this
> same kind of logic, since there's not currently anyplace in EXPLAIN
> output to show nondefault column aliases. It might be more confusing
> than otherwise to use generated aliases in EXPLAIN, even if the original
> aliases conflict.
>
> If we're going to do something like this, now (9.3) would be a good time
> since we already made changes in alias-assignment in the earlier commit.
>
> Comments, better ideas?
>
> regards, tom lane

I'm having a hard time following this. Can you provide a concrete example?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company