Thread: Is there a way to 'unrestrict' drop view?
Hello, 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? Kind regards, Thomas
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: > 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
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