Thread: Don't cascade drop to view
Hi all, i made some views for the slony1 configuration tables in the public schema which refer to tables in the _slony1 schema. My problem now is that if the _slony1 schema is dropped with cascade or slony is uninstalled, these views are also dropped and i have to recreate them if slony is initialized again. Is there a possibility to let the views live there even if the refered schema or tables are dropped? Would a plpgsql Function also be dropped? thx, Peter -- Peter Bauer APUS Software G.m.b.H. A-8074 Raaba, Bahnhofstrasse 1/1 Email: peter.bauer@apus.co.at Tel: +43 316 401629 24 Fax: +43 316 401629 9
Unfortuantely, there is no way around it. Without cascade it won't let you delete the schema or table. Functions will not be dropped. Sim Peter Bauer wrote: > Hi all, > > i made some views for the slony1 configuration tables in the public schema > which refer to tables in the _slony1 schema. My problem now is that if the > _slony1 schema is dropped with cascade or slony is uninstalled, these views > are also dropped and i have to recreate them if slony is initialized again. > Is there a possibility to let the views live there even if the refered schema > or tables are dropped? Would a plpgsql Function also be dropped? > > thx, > Peter >
On Jan 17, 2008, at 8:27 AM, Sim Zacks wrote: > Peter Bauer wrote: >> Hi all, >> i made some views for the slony1 configuration tables in the >> public schema which refer to tables in the _slony1 schema. My >> problem now is that if the _slony1 schema is dropped with cascade >> or slony is uninstalled, these views are also dropped and i have >> to recreate them if slony is initialized again. >> Is there a possibility to let the views live there even if the >> refered schema or tables are dropped? Would a plpgsql Function >> also be dropped? >> thx, >> Peter > >> Unfortuantely, there is no way around it. >> Without cascade it won't let you delete the schema or table. >> Functions will not be dropped. If you dropped tables out from under views, how would you expect them to act if someone were to query them? Inconsistent and unpredictable are just two words I'd use to describe a system that allowed that. However, if these are relatively simple views, you may be able to get away with re-implementing them as functions that return sets of whatever record type your views are. If you're building custom views that depend on a separate package then you're going to be pretty much required to write scripts to generate those view and custom install scripts for the package you're building on. Oh, and document all of that, as well. If you don't do this you'll be stuck managing things by hand which, on all but teeny tiny projects, is a BadThing. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Thu, Jan 17, 2008 at 11:10:25AM -0600, Erik Jones wrote: > If you dropped tables out from under views, how would you expect them > to act if someone were to query them? Inconsistent and unpredictable > are just two words I'd use to describe a system that allowed that. I'd expect it to throw an error that the tables are missing. I ran into this today. All it really requires is that the view definition be parsed at use time rather than at creation time. > However, if these are relatively simple views, you may be able to get > away with re-implementing them as functions that return sets of > whatever record type your views are. As you say, functions are compiled at use time, and hence don't suffer this problem. You can build a view on the function and it should be transparent... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy