Thread: dropping a schema and cross-schema dependencies

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

"George Pavlov" <gpavlov@mynewplace.com> writes:
> 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?

Issue the DROP without CASCADE and read the error message.
        regards, tom lane

> > 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?
>
> Issue the DROP without CASCADE and read the error message.

Well, to be precise I should read the notices preceding the error
message (the error message is the same whether there are within- or
cross-schema dependencies). But, yes, that's what I have been doing, I
was hoping for something a little more graceful. Ideally I want a DROP
SCHEMA in a script to succeed without bothering me if there are no
cross-schema dependencies and only to fail if there are cross-schema
dependencies. So I was hoping I could check ahead of time and in a
definitive way that there are no cross schema dependencies and then
issue a DROP CASCADE. What you are suggesting is to issue a DROP without
CASCADE and grep through the error message before issuing a second DROP
CASCADE, but what am I grepping for? What are all the possible notices
that pertain to cross-schema dependencies? I want to make sure I am not
missing something that I have not seen before. So, for example we might
have a notice like this:
 NOTICE:  table test.employee column first_name depends on type name_ud

So, if the word "table" is followed by a word that contains a period I
have a cross-schema dependency. This could work. I just want to be
comprehensive.

George