DROP OWNED BY doesn't work

So I was fooling with making serial sequences be auto rather than internal
dependencies of their columns, and the regression tests blew up on me:

*** ./expected/dependency.out Mon Nov 21 07:49:33 2005
--- ./results/dependency.out Sat Aug 19 17:46:55 2006
***************
*** 109,113 ****
--- 109,117 ----
DROP USER regression_user2;
ERROR: role "regression_user2" cannot be dropped because some objects depend on it
DROP OWNED BY regression_user2, regression_user0;
+ NOTICE: default for table deptest column a depends on sequence deptest_a_seq
+ ERROR: cannot drop sequence deptest_a_seq because other objects depend on it
DROP USER regression_user2;
+ ERROR: role "regression_user2" cannot be dropped because some objects depend on it
DROP USER regression_user0;
+ ERROR: role "regression_user0" cannot be dropped because some objects depend on it

On investigation I find that DROP OWNED BY tries to drop objects in the
more-or-less-random order that they appear in pg_shdepend. This doesn't
work, at least not without CASCADE. I think people should be able to
assume that DROP OWNED BY RESTRICT will drop all the target users'
objects so long as there are not objects owned by other users that
depend on them.

There's a hack in there now that tries to special-case this for INTERNAL
dependencies, but I think it's misguided. You can run into the problem
without any internal or auto dependencies:

regression=# create user foo;
CREATE ROLE
regression=# \c - foo
You are now connected to database "regression" as user "foo".
regression=> create table tt1 (f1 int);
CREATE TABLE
regression=> create table tt2 (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tt2_pkey" for table "tt2"
CREATE TABLE
regression=> alter table tt1 add foreign key (f1) references tt2;
ALTER TABLE
regression=> drop owned by foo;
NOTICE: constraint tt1_f1_fkey on table tt1 depends on table tt2
ERROR: cannot drop table tt2 because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
regression=>

I think a correct solution probably requires making a list of all
objects to delete by scanning pg_shdepend and then starting to
delete 'em, using the list as "oktodelete" context similar to the
way that dependency.c handles auto/internal objects.

regards, tom lane