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

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Dec 21, 2012 at 9:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm suggesting that we could fix this by emitting something that forces
>> the right alias to be assigned to t2.q:
>>
>> SELECT t1.x, t1.y, t2.z
>> FROM t1
>> JOIN t2 AS t2(x,z)
>> USING (x);

> Sneaky.  I didn't know that would even work, but it seems like a
> sensible approach.

I've been idly hacking away at this over Christmas break, and attached
is a draft patch.  The problems turned out to be considerably more
extensive than I'd realized --- in addition to the stated issue with
forcing input aliases for JOIN USING colums to match, I found that:

* For joins without aliases, we can't qualify join column references,
since obviously there's no relation name to use.  (And we can't add one
without breaking queries, because SQL specifies that an aliased JOIN hides
relation names within it.)  That's okay in simple cases because we can
just print the name of the referenced input column instead.  However,
that doesn't work for merged columns in FULL JOIN USING, because in a full
join a merged output column doesn't behave the same as either input.  The
only solution I can see for this is to force the column aliases for such
columns to be unique query-wide, not just within the join RTE.  Then they
can be referenced without a relation name and still be unambiguous.

* When printing a join's column alias list, we were just blindly printing
the user's original alias list.  However, addition or removal of columns
from either input table invalidates that: we have to be able to add or
remove aliases to match the new column set.

I believe the attached patch covers all cases arising from column
additions, deletions, or renames.  It's awfully large though --- about
1100 lines added to ruleutils.c.  We could possibly make it a bit smaller
if we changed the parser to save more information about JOIN USING
columns, so that we don't need the grotty flatten_join_using_qual hack.
But that would only save about 100 lines, and it would add more elsewhere,
so I'm not sure it's worth the trouble.  (It would also prevent anyone
from trying to use the patch in the back branches, not that I plan to
take the risk of back-patching.)

On the whole I think this is a "must fix" bug, so we don't have a lot of
choice, unless someone has a proposal for a different and more compact
way of solving the problem.

            regards, tom lane