Re: LOCK DATABASE

On Thu, May 19, 2011 at 04:13:12PM -0400, Alvaro Herrera wrote:
> Excerpts from Robert Haas's message of jue may 19 15:32:57 -0400 2011:
> >
> > That's a bit of a self-defeating argument though, since it implies
> > that the effect of taking an exclusive lock via LockSharedObject()
> > will not simply prevent new backends from connecting, but rather will
> > also block any backends already in the database that try to perform
> > one of those operations.
>
> Well, the database that holds the lock is going to be able to run them,
> which makes sense -- and you probably don't want others doing it, which
> also does. I mean other backends are still going to be able to run
> administrative tasks like slon and so on, just not modifying the
> database. If they want to change the comments they can do so after
> you're done with your lock.
>
> Tom has a point though and so does Chris. I'm gonna put this topic to
> sleep though, 'cause I sure don't want to be seen like I'm proposing a
> connection pooler in the backend.

I know I'm late to this party, but just wanted to chime in with support
for the idea that access to a particular database is properly in the
scope for a DBA, and it would be good for it not to require
filesystem/sysadmin action. It seems to me to be a proper serverside
support for poolers or shared hosting setups, or other uses cases,
without going to whole hog. Arguably would probably require versions of
pg_cancel_backend and pg_terminate_backend that operate for the database
owner, as well as superuser.

Perhaps the approach to restricting connections should not be a database
object lock, but rather an admin function that does the equivalent of
flipping datallowconn in pg_database?

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE