Re: Invalidating dependent views and functions

Robert Haas wrote:
> On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey <artacus(at)comcast(dot)net> wrote:
>> Proposal: Add an invalid flag to pg_class. Invalid objects would be ignored
>> when doing dependency checks for DDL statements. And an exception would be
>> thrown when an invalid object is called.
>>
>> This is similar to what Oracle does. And most Oracle tools have find and
>> compile invalid objects with a statement like:
>> ALTER VIEW foo RECOMPILE;
>> ALTER PACKAGE bar RECOMPILE BODY;
>
> Keep in mind that our implementation is apparently quite different
> from Oracle's. Of course I have no idea what they do under the hood,
> but we don't even store the original text of the view. Instead, we
> store a parsed version of the view text that refers to the target
> objects logically rather than by name. That has some advantages; for
> example, you can rename a column in some other table that the view
> uses, and nothing breaks. You can rename a whole table that is used
> by the view, and nothing breaks. Even if we added storage for the
> text of the view, recompiling it might result in some fairly
> astonishing behavior - you might suddenly be referring to tables or
> columns that were quite different from the ones you originally
> targeted, if the old ones were renamed out of the way and new,
> eponymous ones were added.
>
> I'm familiar with the view-dependency-hell problem you mention, having
> fought with it (succesfully, I'm pleased to say, using a big Perl
> script to manage things - and also - obligatory dig here - to work
> around our lack of support for CREATE IF NOT EXISTS) on many
> occasions, but I don't have any brilliant ideas about how to solve it.
> I would like to eventually support ALTER VIEW ... DROP COLUMN; note
> that we do now support ADDING columns to a view using CREATE OR
> REPLACE as long as all the new ones are at the end. But neither of
> those things is going to help with a case like yours, when you want to
> change the type of the column. I'm not really sure what to do about
> that case.
>
> ...Robert
>

I've been using the "source" in information_schema.views rather than
storing the original source. Oracle does indeed store the original
source code for your objects. I don't know what they use to recompile.
But my inclination is that they use the original source. If you alter a
table/column name I believe it will invalidate any dependent views which
will need manually edited before they will compile successfully.

As for Oracle's approach being stupid and not user friendly, OK, maybe
they could automatically try to recompile. But even a manual process is
better than no help at all.

Scott