I had a test schema containing one table.

  olly=# drop schema testing;
  NOTICE:  table testing.testa depends on schema testing

  ERROR:  Cannot drop schema testing because other objects depend on it
  	  Use DROP ... CASCADE to drop the dependent objects too

This seems a little over-restrictive to me.  There is no dependency
outside the schema, so shouldn't that have worked?  I should have
thought CASCADE would be implicit for objects inside the schema to be
dropped.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For the Lord himself shall descend from heaven with a 
      shout, with the voice of the archangel, and with the 
      trump of God; and the dead in Christ shall rise first;
      Then we which are alive and remain shall be caught 
      up together with them in the clouds, to meet the Lord 
      in the air; and so shall we ever be with the Lord."   
                          I Thessalonians 4:16,17 


21 Aug 2002 07:27:22 +0100
Oliver Elphick <olly@lfix.co.uk>
Dropping a schema
Oliver Elphick <olly@lfix.co.uk> writes:
>   olly=# drop schema testing;
>   NOTICE:  table testing.testa depends on schema testing
>   ERROR:  Cannot drop schema testing because other objects depend on it
>   	  Use DROP ... CASCADE to drop the dependent objects too

> This seems a little over-restrictive to me.

It's per spec: SQL92 saith

         3) If RESTRICT is specified, then S shall not contain any per-
            sistent base tables, global temporary tables, created local
            temporary tables, views, domains, assertions, character sets,
            collations, or translations.

            Note: If CASCADE is specified, then such objects will be dropped
            by the effective execution of the SQL schema manipulation state-
            ments specified in the General Rules of this Subclause.

Also, it seems the safest behavior to me.  "rmdir dir" won't remove a
nonempty directory; isn't that a pretty close analogy?

			regards, tom lane

Wed, 21 Aug 2002 10:02:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Dropping a schema 
On Wed, 2002-08-21 at 15:02, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> >   olly=# drop schema testing;
> >   NOTICE:  table testing.testa depends on schema testing
> >   ERROR:  Cannot drop schema testing because other objects depend on it
> >   	  Use DROP ... CASCADE to drop the dependent objects too
> 
> > This seems a little over-restrictive to me.
> 
> It's per spec: SQL92 saith
...
> Also, it seems the safest behavior to me.  "rmdir dir" won't remove a
> nonempty directory; isn't that a pretty close analogy?

Not really, seeing that you can't say "mkdir directory (containing these
files)".  An implicit cascade *inside* the schema seems an appropriate
parallel to "CREATE SCHEMA ... (CREATE TABLE ...)".  After all, we don't
have to say "DROP TABLE ... CASCADE" because the table has rows in it!

But if that's what the spec says...

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For the Lord himself shall descend from heaven with a 
      shout, with the voice of the archangel, and with the 
      trump of God; and the dead in Christ shall rise first;
      Then we which are alive and remain shall be caught 
      up together with them in the clouds, to meet the Lord 
      in the air; and so shall we ever be with the Lord."   
                          I Thessalonians 4:16,17 


21 Aug 2002 16:07:54 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: Dropping a schema
Oliver Elphick <olly@lfix.co.uk> writes:
> On Wed, 2002-08-21 at 15:02, Tom Lane wrote:
>> Also, it seems the safest behavior to me.  "rmdir dir" won't remove a
>> nonempty directory; isn't that a pretty close analogy?

> Not really, seeing that you can't say "mkdir directory (containing these
> files)".  An implicit cascade *inside* the schema seems an appropriate
> parallel to "CREATE SCHEMA ... (CREATE TABLE ...)".  After all, we don't
> have to say "DROP TABLE ... CASCADE" because the table has rows in it!

Hm.  I could see an argument for being willing to auto-drop stuff that
had been made that way (inside CREATE SCHEMA) but not stuff that was
made by separate commands.  But the spec doesn't seem to make any such
distinction: RESTRICT is RESTRICT.  In any case, I like the behavior as
it is, so I'm not gonna go out of my way to change it...

			regards, tom lane

Wed, 21 Aug 2002 12:12:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Dropping a schema 