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