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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Daniel Cristian Cruz" <danielcristian(at)gmail(dot)com> writes:
>> CREATE TABLE a (
>>  id_a serial primary key,
>>  v text
>> );
>> CREATE TABLE b (
>>  id_b serial primary key,
>>  id_a integer REFERENCES a (id_a),
>>  v text
>> );
>> CREATE TABLE c (
>>  id_c serial primary key,
>>  id_b integer references b (id_b),
>>  v text
>> );
>
>> CREATE VIEW cba AS
>>  SELECT c.v AS vc, b.v AS vb, a.v AS va
>>  FROM c
>>  JOIN b USING (id_b)
>>  JOIN 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".  There'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.  The 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.  That 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.

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