Re: BUG #5070: Drop <schema> cascade fails on 8.4

"Cedric Berger" <cedric(at)berger(dot)to> writes:
> I've a schema with actually 409 tables.
> I've made a default installation of postgresql on the mac
> (Installer version Version 8.4.1-1 from enterprise db).

> Unfortunately, since I upgraded from 8.3, I cannot drop
> these kind of schema anymore:

> postgres=# drop schema hard_12 cascade
> postgres-# ;

Works for me ... however such a command is certainly going to take a lot
of locks (at least 409, maybe many more if these tables have lots of
indexes). So it's definitely possible that you'd overrun the default
lock table size.

> WARNING: out of shared memory
> ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.

> I've tried to double max_locks_per_transaction, double the values
> of SHMMAX/SHMALL, there is no difference...

Did you actually restart the server after increasing
max_locks_per_transaction?

I suspect that you have an exceptionally small amount of free shared
memory because OSX's default shared memory limits are so small. If
initdb chose a small value of max_connections because of that, that'd
be contributing to your problem, because the actual lock table size
is max_locks_per_transaction * max_connections.

OSX is extremely picky about shared memory parameters; there is only
one right way to do it, and that's described in our docs at
http://www.postgresql.org/docs/8.4/static/kernel-resources.html#SYSVIPC
In recent releases the right way is to create a /etc/sysctl.conf file
setting *all five* SHM parameters to valid values, then reboot.
If "sysctl -a | grep sysv.shm" doesn't show the expected values,
you didn't get it right.

regards, tom lane