Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view

On Fri, Jun 3, 2011 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Daniel Cristian Cruz" <danielcristian@gmail.com> writes:
>> CREATE TABLE a (
>> =A0id_a serial primary key,
>> =A0v text
>> );
>> CREATE TABLE b (
>> =A0id_b serial primary key,
>> =A0id_a integer REFERENCES a (id_a),
>> =A0v text
>> );
>> CREATE TABLE c (
>> =A0id_c serial primary key,
>> =A0id_b integer references b (id_b),
>> =A0v text
>> );
>
>> CREATE VIEW cba AS
>> =A0SELECT c.v AS vc, b.v AS vb, a.v AS va
>> =A0FROM c
>> =A0JOIN b USING (id_b)
>> =A0JOIN a USING (id_a);
>
>> ALTER TABLE c ADD id_a integer;
>
>> [ view definition now fails due to multiple "id_a" columns ]
>
> I'm inclined to write this off as "so don't do that". =A0There's nothing
> that pg_dump can do to make this work: it has to use the USING syntax
> for the join, and that doesn't offer any way to qualify the column name
> on just one side. =A0The only possible fix would be to try to make ALTER
> TABLE reject the addition of the conflicting column name to "c" in the
> first place. =A0That doesn't seem very practical; it would require ALTER
> TABLE to do a tremendous amount of analysis, and exclusively lock all
> the dependent views, and then lock all the other tables used in the
> views, and so on.
>
> Personally my advice is to avoid USING: it wasn't one of the SQL
> committee's better ideas.

I don't understand why we can't just translate the USING into some
equivalent construct that doesn't involve USING.  I proposed that a
while ago and you shot it down, but I didn't find the reasoning very
compelling.

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