Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

On Mon, May 26, 2014 at 10:39 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * ash (ash(at)commandprompt(dot)com) wrote:
>> This came up recently on general list (and I've just hit the same issue today):
>> http://www.postgresql.org/message-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com
>>
>> Why couldn't postgres re-create the dependent views automatically? I
>> mean it knows which views depend on the altered column and keeps the
>> view definition, no?
>
> Might be pretty complicated in the end..
>
>> Would a patch likely to be accepted? How hard do you feel this might be
>> to implement? Any caveat that defeats the purpose of such feature?
>
> It'd need to be explicitly requested, eg a 'CASCADE' option.

Why? Would any sane person NOT want this behavior?

I think the question here is whether there's any way to make this work
at all, not whether we'd want it if we could get it. Consider:

CREATE OR REPLACE VIEW v AS SELECT a + 0 FROM t;

If we drop the view, change the column type of t.a, and re-execute the
view, + might resolve to a different operator than before (or no
operator, at all). Furthermore, the operator to which it resolves
will depend on the search path at the time the CREATE OR REPLACE VIEW
command is executed.

Now, consider the situation in which we want to achieve the same
result without having to drop and recreate v. When the column type of
t.a is changed, we can use the dependencies to figure out that v might
be impacted. We can trace through the rewrite rule to find out where
column t.a is referenced. And ... then what? All we know about t.a
is that we're applying some operator to it, which is specified by OID.
The rewrite rule doesn't tell us the actual *name* by which the
operator was referenced in the original view text, nor does it tell us
the search path that was in effect at that time. If it did, we could
pick the same operator for + that would have been used had t.a been of
the new type originally, but as it is, we can't.

Now maybe there are options other than trying to reproduce what the
original CREATE OR REPLACE statement would have done against the new
type. For example, we could look through views that depend on t.a and
rewrite each reference to that column to t.a::oldtype. This might
lead to odd results with multiple nested casts and generally funny
behavior if the column is re-typed multiple times; but maybe there's
some way to fix that. Also, it might not really be the semantics you
want if you were hoping the type update would truly cascade. But it
might still be better than a sharp stick in the eye, which is kinda
what we offer today.

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