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

"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.

regards, tom lane