One of the things that came out of the clustering session is a need for
a LOCK DATABASE command. Primarily to be able to drop databases across
nodes in a cluster, but later chats lead to ideas about upgrading
databases' schemas and such operations that need to ensure that no one
else is accessing the database.
Some guys proposed that this could be implemented by changing some
pg_hba.conf rules on the fly, but to me that seems a really ugly hack
and not a real solution. (You could equally propose that all CONNECT
privileges to a database should be granted via some role specifically
dedicated to this task, and that this role would be revoked permission
when you want to lock out connections. This seems really ugly as well.)
Since DROP DATABASE requires to be called outside of a transaction, it
is necessary to acquire a session-level lock, which wouldn't be released
at the end of the locking transaction. The problem with this idea
was that it'd need an UNLOCK DATABASE command to go with it -- which
sucks and I didn't want to add to this proposal, but Andres didn't want
to hear about that.
So we would have a new command LOCK DATABASE [FOR SESSION] or something
like that; the pooler software would call that and then kill other
existing application connections (using pg_terminate_backend() perhaps),
then drop the database. This LOCK DATABASE thingy would just be a
simple function on top of LockSharedObject. Since establishing a new
connection requires grabbing a lock on the database via
LockSharedObject, things would Just Work (or at least so it seems to
me).
UNLOCK DATABASE would be needed to release a session-level lock acquired
by LOCK DATABASE FOR SESSION for the cases where you want to lock a
database to safely do schema upgrades and the like.
(I was thinking that we already need a simple LockDatabase wrapper on
top of LockSharedObject, but that's really a nice and small cleanup of
existing code and not a new feature.)
Thoughts?
--
Álvaro Herrera <alvherre@alvh.no-ip.org>
