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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>
>> 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.

This could be a showstopper indeed. We can look up view def in pg_views
view, but it doesn't include any schema references unless they were
explicit in the CREATE VIEW statement.

On the other hand, pg_dump *can* work around this: if you dump a view
that has been defined when a specific search_path was in effect, you'll
get correct definition in the schema dump.

So why can't we try to learn from pg_dump?

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

No, casting back to oldtype totally defeats the purpose, at least for my
usecase.

--
Alex