Re: patch: Add columns via CREATE OR REPLACE VIEW - Mailing list pgsql-hackers

>> I think the only thing we need to agree on is that no future implementation
>> of CREATE OR REPLACE VIEW will ever implicitly rename a column. If we agree
>> on column name as a measure of column identity, then the change I'm
>> proposing is forward-compatible with any other enhancements we may want to
>> make later.
>
> hm... so what would this output?
>
> CREATE VIEW a AS (select 1 as a, 2 as b);
> CREATE VIEW b AS (select x,y FROM a AS a(x,y))
> CREATE OR REPLACE VIEW a AS (select 1 as b, 'two' as c, 3 as a);
> SELECT * FROM b;

I'm not too familiar with the syntax "a AS a(x, y)" but I think it's
asking that the first two columns of a (whatever they are at the
moment) be aliased to x and y.  If we allow reordering columns, it's
going to change the meaning of a number of different expressions.  In
addition to the above, you have to worry about at least:

(1) INSERT INTO foo VALUES (a, b, c)
(2) COPY foo FROM wherever
(3) SELECT * FROM foo

Were I implementing the ability to reorder columns (which I have no
current plans to do), I think the logical thing to do would be to
decree that all of this is the user's problem.  If you sensibly refer
to columns by name, then you are guaranteed to always be referencing
the same set of columns.  If you assume that you know the position of
the columns, you assume the risk of getting burned if that ordering
changes.  This is already true: even without the ability to reorder
columns, a table or view can still be dropped and recreated with a
different column ordering, and any client code that stupidly relies on
the columns being in the same order that they were before will blow
up.  If people are writing code this way, they should either (1) fix
it to include explicit target lists or (2) not ever change the order
of their table columns.  This is true whether or not reordering
columns requires dropping the object in question and all of its
dependencies, or whether it can be done in place, and is not an
argument for refusing to make it easier to do in-place.

With respect to your particular example, I think CREATE OR REPLACE
VIEW should fail with an error, just as this case does:

CREATE VIEW a AS (select 1 as a, 2 as b);
CREATE VIEW b AS (select x,y FROM a AS a(x,y));
ALTER TABLE a RENAME TO a_old;  -- move a out of the way so we can
change the definition
CREATE OR REPLACE VIEW a AS (select 1 as b, 'two' as c, 3 as a);
CREATE OR REPLACE VIEW b AS (select x,y FROM a AS a(x,y)); -- recreate
b based on new definition of a
ERROR:  cannot change data type of view column "y"

As Tom said upthread, columns should only be allowed to be dropped or
retyped if they have no dependencies.  This case is a little weird
because normally the dependency would follow the name, but here
because of the a(x, y) syntax it has to follow the column position,
but it seems clear to me that the type change can't silently propagate
down into view b, so making it error out is the only logical
alternative.

> What about this?
>
> CREATE OR REPLACE VIEW a AS (select 1 as b, 2 as c, 'three as a);
> SELECT * FROM b;

This seems well defined.  a now has columns b-c-a with types
int-int-varchar.  b gets a.b as x and a.c as y, so I expect to get (1,
2).  If he had written CREATE VIEW b AS (select a as x,b as y FROM a)
then the statement above would fail due to a dependency on the type of
a.

Of course, accepting the patch I submitted doesn't require us to ever
support any of this, since it makes no attempt to reorder or retype
any existing columns.  It only allows adding new columns at the end.
If we want to stick with the approach of "don't ever reorder columns",
we could decree that the goal is for CREATE OR REPLACE VIEW to allow
adding new columns and retyping of columns without dependencies, but
that renaming, dropping, and potentially reordering would have to be
done using an ALTER VIEW command.

I'm afraid we're getting off into a discussion of how to support
reordering of columns which I find fascinating but not necessarily
relevant to the patch at hand.  I can't think of any imaginable
scenario in which the ability to add new columns at the end of an
existing view via CREATE OR REPLACE VIEW causes any problem for any
feature we might want to implement in the future, and it's clearly
useful.  It takes me about half an hour to add a column to a
particular view on one of the systems I work with because of the need
to update all the dependent objects, and this would reduce it to about
10 seconds.

...Robert