Re: Rethinking locking for database create/drop vs connection startup

Simon Riggs <simon@2ndquadrant.com> writes:
> On Wed, 2006-05-03 at 16:15 -0400, Tom Lane wrote:
>> If dropdb() takes such a lock before it checks for active
>> backends, then the connection sequence can look like this:

> Many people never CREATE or DROP databases. They just do everything in
> the default database (name is release dependent) - at least on their
> main system(s). It would be valid to optimize for that case.

I'm not particularly concerned about people with only a couple of
databases --- reading the flat file isn't going to take any meaningful
amount of time for them anyway.  It's the folks with hundreds of
databases who might have a beef.  But those are exactly the people
who need create/drop database to be bulletproof.

As I've been working on this patch I've found that it will clean up a
whole lot of related issues, so I'm getting more and more convinced
it's the Right Thing.  Some points:

* Connecting will actually take RowExclusiveLock (ordinary writer's
lock), while CREATE DATABASE takes ShareLock on the template DB, and
of course DROP/RENAME DATABASE take AccessExclusiveLock.  This provides
for the first time an absolute guarantee that CREATE DATABASE gets a
consistent copy of the template: before we could never ensure that
someone didn't connect to the template and change it while the copy was
in progress.  At the same time, two CREATE DATABASEs can safely use the
same template, and of course two concurrent connections don't block
each other.

* Since we're trying not to take any table-level exclusive locks on
pg_database anymore, we need a different solution in flatfiles.c to
ensure only one transaction writes the flat file at a time.  To do this
I'm going to have a dedicated lock, used only in the flatfile code, that
is taken just before trying to write the file and held till commit
(which is immediately after).  This eliminates the former risk of
deadlock associated with manual updates to pg_database, and as a bonus
holds the exclusive lock for a much shorter period of time.
        regards, tom lane