Re: Re: About "Allow VIEW/RULE recompilation when the underlying tables change"

On Dec 19, 2009, at 4:38 PM, Robert Haas wrote:
> On Sat, Dec 19, 2009 at 1:56 PM, Caleb Welton <cwelton(at)greenplum(dot)com> wrote:
>> I maintain that the approaches that inform the user that they have met that
>> condition via ALTER statement failures (Postgres/DB2/Microsoft Bound Views)
>> have certain advantages over databases that allow the update but may
>> silently leave views in an usable state (Oracle, Terradata), in that at
>> least the user Knows when they have to re-examine their views.
>
> Agreed.
>
>> As far as I can tell there are three approaches that could be taken to help
>> address this problem:
>> 1) DB2 like approach - try to perform rewrites where able, but if the
>> rewrite fails then the alter operation fails. Would allow simple edits such
>> as ALTER TYPE that are only changes in typmod, or if done more ambitiously
>> would allow numbers to be changed to other numbers. But as Robert says this
>> quickly approaches the territory of black magic.
>
> And it can easily lead to silent breakage - e.g. if you change an
> integer column to text, the view's attempt to coerce the text back to
> integer will continue working as long as that coercion is valid for
> all the data the view examines, but you have to think the user had a
> reason for changing the type...

Or we could simply disallow those types of cases. It's not optimal, but would still provide a lot of benefit in other cases.

BTW, +1 on allowing something akin to SELECT * to propagate ADD COLUMN, though for compatibility we should use something other that "SELECT *".
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net