Re: LOCK DATABASE

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?"