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

On Tue, May 27, 2014 at 11:20 PM, ash <ash(at)commandprompt(dot)com> wrote:
>> 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?

Well, pg_dump is trying to do something different than what you're
trying to do here. pg_dump wants to make sure that the view, when fed
back into psql, creates the same view that exists now, regardless of
whether that's what the user created originally. For example, if a
view is created referring to table foo, and table foo is later renamed
to bar, then pg_dump wants to (and does) dump a statement referring to
bar, not foo - even if there's a *new* table called foo against which
the view could have been defined. Similarly, pg_dump will
schema-qualify functions and operators, or not, based on whether
that's necessary to reference the exact same operators that were
selected when the original CREATE VIEW command was run, regardless of
whether the original references were schema-qualified. None of that
involves answering hypothetical questions; but what you want to do
does, and that I think is the problem in a nutshell.

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