Is there a way to 'unrestrict' drop view?

On Thu, Jul 21, 2011 at 3:20 PM, Thomas Pasch <thomas.pasch@nuclos.de> wrote:

I would like to recreate/replace a view, but there are 'dependant
objects' on it. Is there a way to 'unrestrict' the dependant check in
the current transaction, like it could be done with certain constraints?

Hi,

Nice idea, but i think there isn't a way to do that.
You will have to drop and re-create the objects in the correct order,
best in a single transaction.

I can imagine that that can be nasty, even apart from the hassle of
cutting and pasting + testing that code. You might be needing those
objects in a running system.
But then what would it mean to to what you suggest? The dependent
objects could never function while the view does not exist, so it ends
up being much the same as drop+create.
Except that you are changing the view, so you might also need to
change the depending objects..

Cheers,

WBL
--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw

Hi,

well, the reason I'm asking is that this *is* posible in Oracle DB. For
me it looks like that the DB knows that the view is broken. You can't
use it, *but* it is still there (and it will be usable again when the
view query is valid again).

I completely agree that the view should be usable again at the end of
transaction (even thus Oracle DB doesn't impose that either), but drop
and re-create the objects in correct order is painful.

The heart of the my pain is that a program I use works like this. I
would like to migrate the DB beneath it...

Cheers,

Thomas

Am 22.07.2011 10:26, schrieb Willy-Bas Loos:

Thomas Pasch <thomas.pasch@nuclos.de> writes:

well, the reason I'm asking is that this *is* posible in Oracle DB. For
me it looks like that the DB knows that the view is broken. You can't
use it, *but* it is still there (and it will be usable again when the
view query is valid again).

I completely agree that the view should be usable again at the end of
transaction (even thus Oracle DB doesn't impose that either), but drop
and re-create the objects in correct order is painful.

Well, if the dependent objects don't need to be touched because the
view's API (its output column set) isn't changing, then you can use
CREATE OR REPLACE VIEW.

If the output column set *is* changing, you need to redefine all the
dependent objects anyway. Oracle may be willing to guess at what
should happen to them, but Postgres isn't.

regards, tom lane

Thomas Pasch wrote:

well, the reason I'm asking is that this *is* posible in Oracle DB.

For

me it looks like that the DB knows that the view is broken. You can't
use it, *but* it is still there (and it will be usable again when the
view query is valid again).

True, but Oracle pays a price for it. There is never a guarantee that
all objects in the database are consistent, and in fact you're always
likely to have a number of 'invalid' objects around that might fail or
not if you use them.

The heart of the my pain is that a program I use works like this. I
would like to migrate the DB beneath it...

I'd say that a program that changes views on the fly has a questionable
design, but obviously that won't help you.

You could automatically find out all dependent views (via pg_depend),
get their DDL (with pg_get_viewdef()) and drop and recreate them in
order.
That's painful of course.

Yours,
Laurenz Albe