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

I understood that USING is not a cool thing to use inside views, will avoid
it.

Still gonna use it in reports and applications, since there is no risk to
affect backups and raises an error.

It would be nice to had an NOTICE or WARNING about the USING clause inside
VIEWs, saying that it's not recommended since a schema change would crash
backups.

2011/6/3 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

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

--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル