dropping a schema and cross-schema dependencies

From: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
To: "pgsql-sql" <pgsql-sql(at)postgresql(dot)org>
Subject: dropping a schema and cross-schema dependencies
Date: 2007-04-06 22:09:21
Message-ID: 8C5B026B51B6854CBE88121DBF097A86A2CAF1@ehost010-33.exch010.intermedia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I would like to be able to drop a schema with all of its objects, but
don't want to accidentally drop objects that are in other schemas. If
there are such objects I'd prefer the drop to fail. If I use DROP SCHEMA
FOO CASCADE I run the risk of dropping objects from other schemas that
depend on objects in FOO (e.g. a table that uses a domain/user defined
datatype from FOO). I don't particularly like such cross-schema
dependencies but they are easy to create acidentally and I don't want to
blow out a big table.

Any thoughts on this? "Talk to the people who wrote the SQL standard" is
one response, of course, but what do you all think is a reasonable
behavior here? Anyone else see this as an issue? Does this warrant an PG
extension to SQL? How do other DBMSs do it?

As an immediate solution can anyone share a comprehensive query to
INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any
cross-schema dependencies and halt before issuing a DROP with a CASCADE?

TIA,

George

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2007-04-06 23:39:38 Re: dropping a schema and cross-schema dependencies
Previous Message paallen 2007-04-06 16:57:06 Update Field with function/data from other tables?