Thread: LOCK DATABASE

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>

On Tue, May 17, 2011 at 10:21 PM, Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:
>
> 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.
>

So we the lock will be released at end of the session or when the
UNLOCK DATABASE command is invoked, right?
A question: why will we beign so rude by killing other sessions
instead of avoid new connections and wait until the current sessions
disconnect?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Wed, May 18, 2011 at 1:02 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> So we the lock will be released at end of the session or when the
> UNLOCK DATABASE command is invoked, right?
> A question: why will we beign so rude by killing other sessions
> instead of avoid new connections and wait until the current sessions
> disconnect?

There were multiple alternatives suggested, which is probably useful to outline.

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.

And I'm not sure it'll be the right thing to lock out "everything
except the present connection."

When doing Slony "cluster surgery," it would NOT be acceptable to
restrict DB activity to a single connection - multiple slon processes
legitimately need to get in.

And that sure does look a lot more like something that's appropriate
to manage via fiddling with pg_hba.conf than via something smelling
like "restrict to something looking a lot like single user mode."

2.  Jan Wieck pointed out that shutting off access to users would be
very well accomplished via allowing their access through a connection
pool (e.g. - like pgbouncer, pgpool), and cutting them off there.

That seems like a better mechanism to me, too, though we've not been
able to use any of those pools with our apps thus far.

3.  There's some analogy to be drawn against pg_ctl...  It has 3 modes
for shutdown:

smart - quits once all clients have disconnected
fast - quit, with proper shutdown of all clients
immediate - quit without complete shutdown

Those are all potentially legitimate "modes" for this lockdown, as
there will be cases where you want to be friendly to applications, as
well as cases where "We right well announced this 3pm outage to
everyone, and, it being 3:00:01, I'm booting you off Right Now.

I thought about #3 during the discussion, but didn't bring it up then.

If the mechanism:
- Doesn't offer the ability to leave Needful Processes (notably,
replication processes) running
- Doesn't allow [friendly/unfriendly] operation modes (as in #3)
then I think the use cases will be a bit thin.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Excerpts from Christopher Browne's message of mié may 18 18:33:14 -0400 2011:
> On Wed, May 18, 2011 at 1:02 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> > So we the lock will be released at end of the session or when the
> > UNLOCK DATABASE command is invoked, right?
> > A question: why will we beign so rude by killing other sessions
> > instead of avoid new connections and wait until the current sessions
> > disconnect?
> 
> There were multiple alternatives suggested, which is probably useful to outline.
> 
> 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.

> And I'm not sure it'll be the right thing to lock out "everything
> except the present connection."
> 
> When doing Slony "cluster surgery," it would NOT be acceptable to
> restrict DB activity to a single connection - multiple slon processes
> legitimately need to get in.

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.

> And that sure does look a lot more like something that's appropriate
> to manage via fiddling with pg_hba.conf than via something smelling
> like "restrict to something looking a lot like single user mode."

I really dislike the idea of having to edit config files for this kind
of thing.  I mean, sure it can be made to work, but it seems the wrong
tool for the job.

> 2.  Jan Wieck pointed out that shutting off access to users would be
> very well accomplished via allowing their access through a connection
> pool (e.g. - like pgbouncer, pgpool), and cutting them off there.
> 
> That seems like a better mechanism to me, too, though we've not been
> able to use any of those pools with our apps thus far.

Yeah, it would work except when it doesn't; it would force you to use a
tool that you may not otherwise need.  So this also seems to me the
wrong solution.

> 3.  There's some analogy to be drawn against pg_ctl...  It has 3 modes
> for shutdown:
> 
> smart - quits once all clients have disconnected
> fast - quit, with proper shutdown of all clients
> immediate - quit without complete shutdown
> 
> Those are all potentially legitimate "modes" for this lockdown, as
> there will be cases where you want to be friendly to applications, as
> well as cases where "We right well announced this 3pm outage to
> everyone, and, it being 3:00:01, I'm booting you off Right Now.

Well, that's a policy decision.  I mean you, as the DBA or as the tool
writer can decide to boot everyone immediately or wait until they are
done with the current transaction or whatever.  I'm just offering the
mechanism to lock out new connections until you're finished with what
you want to do.

> If the mechanism:
> - Doesn't offer the ability to leave Needful Processes (notably,
> replication processes) running
> - Doesn't allow [friendly/unfriendly] operation modes (as in #3)
> then I think the use cases will be a bit thin.

I think we're clear in both fronts :-)

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

On May 18, 2011, at 6:11 PM, Alvaro Herrera wrote:

> Excerpts from Christopher Browne's message of mié may 18 18:33:14 -0400 2011:
>> On Wed, May 18, 2011 at 1:02 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
>>> So we the lock will be released at end of the session or when the
>>> UNLOCK DATABASE command is invoked, right?
>>> A question: why will we beign so rude by killing other sessions
>>> instead of avoid new connections and wait until the current sessions
>>> disconnect?
>>
>> There were multiple alternatives suggested, which is probably useful to outline.
>>
>> 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.


How would this differ from just UPDATE pg_database SET datallowconn = FALSE for the databases in question?

Regards,

David
--
David Christensen
End Point Corporation
david@endpoint.com

Excerpts from David Christensen's message of jue may 19 00:55:36 -0400 2011:

> How would this differ from just UPDATE pg_database SET datallowconn = FALSE for the databases in question?

Several ways actually.  First, it is automatically gone when the locking
session disconnects (so it clean ups after itself).  Second, it doesn't
require manually updating the catalogs, which is frowned upon (with good
reason).  Third, the database owner could do it, not just superuser.


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

On Thursday, May 19, 2011 06:55:36 AM David Christensen wrote:
> On May 18, 2011, at 6:11 PM, Alvaro Herrera wrote:
> > Excerpts from Christopher Browne's message of mié may 18 18:33:14 -0400
2011:
> >> On Wed, May 18, 2011 at 1:02 AM, Jaime Casanova <jaime@2ndquadrant.com>
wrote:
> >>> So we the lock will be released at end of the session or when the
> >>> UNLOCK DATABASE command is invoked, right?
> >>> A question: why will we beign so rude by killing other sessions
> >>> instead of avoid new connections and wait until the current sessions
> >>> disconnect?
> >>
> >> There were multiple alternatives suggested, which is probably useful to
> >> outline.
> >>
> >> 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.
>
> How would this differ from just UPDATE pg_database SET datallowconn = FALSE
> for the databases in question?
Automated lock release on session end. Which imo is quite important...

andres

On Wed, May 18, 2011 at 7:11 PM, Alvaro Herrera
<alvherre@commandprompt.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, 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.
Second, it relies on the fact that a new connection briefly grabs a
lock on the database that is then released.  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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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@commandprompt.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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Robert Haas's message of jue may 19 10:18:20 -0400 2011:
>> 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.

I can't see getting rid of that lock, since we'd simply have to invent
some other interlock for new connections vs. DROP DATABASE.  However,
I do think that we might sometime need to convert it to a session lock
that's held for the life of the backend.  If this feature can't cope
with that, that'd be a potential problem.
        regards, tom lane

Excerpts from Tom Lane's message of jue may 19 13:34:13 -0400 2011:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from Robert Haas's message of jue may 19 10:18:20 -0400 2011:
> >> 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.
> 
> I can't see getting rid of that lock, since we'd simply have to invent
> some other interlock for new connections vs. DROP DATABASE.  However,
> I do think that we might sometime need to convert it to a session lock
> that's held for the life of the backend.  If this feature can't cope
> with that, that'd be a potential problem.

The following things acquire a lock on database:
ALTER DATABASE SETALTER DATABASE OWNERCOMMENT ON DATABASE

So as far as features that would cause a problem if we ever decide to
take a lock on database for the duration of the whole session, this
isn't the first one.  We'd have to invent a fix for those other things
anyway.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Tom Lane's message of jue may 19 13:34:13 -0400 2011:
>> I can't see getting rid of that lock, since we'd simply have to invent
>> some other interlock for new connections vs. DROP DATABASE.  However,
>> I do think that we might sometime need to convert it to a session lock
>> that's held for the life of the backend.  If this feature can't cope
>> with that, that'd be a potential problem.

> The following things acquire a lock on database:

>  ALTER DATABASE SET
>  ALTER DATABASE OWNER
>  COMMENT ON DATABASE

> So as far as features that would cause a problem if we ever decide to
> take a lock on database for the duration of the whole session, this
> isn't the first one.  We'd have to invent a fix for those other things
> anyway.

Only if all the locks involved are exclusive ... which is not what
I was suggesting, and not what they are now IIRC.
        regards, tom lane

On Thu, May 19, 2011 at 12:57 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> 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@commandprompt.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.

I suggest a further different option, namely that perhaps we need to
have more about session management alongside the backend.  The
"loosey-goosey" characterization would be "integrate pgbouncer into
core".

If we have some mass session/connection management alongside the
database, then that enables managing connections en masse.

Today, the "right way" involves "install pgbouncer, pgpool, or some
connection pool manager."   I know there was discussion of doing
something like this a couple years back; I wonder if it's time to
consider that again.

Integrating in a connection manager seems more useful than "lock database"...
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Thu, May 19, 2011 at 1:48 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
>> I can't see getting rid of that lock, since we'd simply have to invent
>> some other interlock for new connections vs. DROP DATABASE.  However,
>> I do think that we might sometime need to convert it to a session lock
>> that's held for the life of the backend.  If this feature can't cope
>> with that, that'd be a potential problem.
>
> The following things acquire a lock on database:
>
>  ALTER DATABASE SET
>  ALTER DATABASE OWNER
>  COMMENT ON DATABASE
>
> So as far as features that would cause a problem if we ever decide to
> take a lock on database for the duration of the whole session, this
> isn't the first one.  We'd have to invent a fix for those other things
> anyway.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Excerpts from Robert Haas's message of jue may 19 15:32:57 -0400 2011:
> On Thu, May 19, 2011 at 1:48 PM, Alvaro Herrera

> > The following things acquire a lock on database:
> >
> >  ALTER DATABASE SET
> >  ALTER DATABASE OWNER
> >  COMMENT ON DATABASE
> >
> > So as far as features that would cause a problem if we ever decide to
> > take a lock on database for the duration of the whole session, this
> > isn't the first one.  We'd have to invent a fix for those other things
> > anyway.
> 
> 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.

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

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@rice.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

On Thu, May 26, 2011 at 12:28 PM, Ross J. Reedstrom <reedstrm@rice.edu> wrote:
> 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?

To me, that seems like a better approach, although it's a little hard
to see how we'd address Alvaro's desire to have it roll back
automatically when the session disconnected.  The disconnect might be
caused by a FATAL error, for example.

I'm actually all in favor of doing more things via SQL rather than
configuration files.  The idea of some ALTER SYSTEM command seems very
compelling to me.  I just don't really like this particular
implementation, which to me seems far too bound up in implementation
details I'd rather not rely on.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company