Thread: view OIDs

Hi,

I use a lot of views in my DB, many of whom depend on each other (ie
views of views).  When I alter one of the subviews, all the views that
depend on it fail.  Postgres complains that the view with the old OID no
longer exists.  The fix is to drop and create all views that depend on
the altered view.  This can get really tedious.

Is there any fix for this?

If there isnt, I was thinking of trying to implement something.  I can
think of several possible solutions:

1) Allow users to create views with particular OID's.  For example
something like CREATE VIEW WITH OID=....  Seems like the simplest solution.

2) On using views that depend on a subview that has changed, if OID
fails, fallback to view name and use that.  It seems that postgres is
aware that another view with the same name exists, as the error is
different in this case than if the view is simply absent.  This would
solve the problem, but it would be nice to update the OIDs somehow.

3) Upon creating a view with a certain name, make all views that depend
on it point to it with the correct OID.

David Kaplan

"David M. Kaplan" <dmkaplan@ucdavis.edu> writes:
> I use a lot of views in my DB, many of whom depend on each other (ie
> views of views).  When I alter one of the subviews, all the views that
> depend on it fail.  Postgres complains that the view with the old OID no
> longer exists.  The fix is to drop and create all views that depend on
> the altered view.  This can get really tedious.

7.3 will refuse to drop a view that has such dependencies unless you
DROP CASCADE --- meaning that the dependent views go away too.

That may not be the most user-friendly approach, but at least you'll
know what definitions you need to reload.  (I am hoping to arrange
things so that if you don't CASCADE, it will tell you all the dependent
objects, not just error out after finding the first one.)

            regards, tom lane