Re: LOCK DATABASE

Excerpts from Robert Haas's message of jue may 19 10:18:20 -0400 2011:
> On Wed, May 18, 2011 at 7:11 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> >> 1.  I suggested that this looks a lot like the controls of pg_hba.conf
> >>
> >> When our DBAs are doing major management of replication, they are
> >> known to reconfigure pg_hba.conf to lock out all users save for the
> >> one used by Slony.
> >
> > Yeah, I mentioned this but I think it actually sucks.
>
> Why? I don't really see why this sucks.

Well, firstly because you need to involve the sysadmin to be able to
write the file. (If you're considering a proposal to move adminpack
into core, I recommend caution.) Second, because then the database
owner can't do it. Third, because the business of having to
programatically edit files is a pain in the butt. Fourth, it doesn't
fix itself it something goes wrong.

> > Well, I don't intend to lock "everything except the present connection".
> > The only thing this LOCK DATABASE does is prevent the establishment of
> > new connections.  Existing connections can continue to exist and work.
> > So you do the LOCK DATABASE, then boot whoever shouldn't be allowed
> > (which is a separate step that needs to be taken), then do your deed.
> > If you want the slon connections to persist, just don't terminate them.
>
> This strikes me as a hack. First, it's completely inconsistent with
> how we lock tables or rows. A lock means you are the only one
> accessing an object, not just that new accesses are locked out.

It doesn't mean that -- you can already get FOR SHARE locks on rows and
other non-blocking locks. Besides, the fact that databases are not
locked out while the connection exists is a well known fact and I very
much doubt that it's going to change.

> Second, it relies on the fact that a new connection briefly grabs a
> lock on the database that is then released.

Yes. This is well known and it's not going away.

> If we happened (for whatever reason) to want to change that to a
> session lock, or get rid of it entirely, then this would break.

That would break other things too, so I don't see it as a problem.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support