Re: Use of 'cp -r' in CREATE DATABASE - Mailing list pgsql-hackers

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>>How about (even quicker and dirtier) putting a limited loop (say 5 
>>iterations?) with a small delay in it around the check for whether or 
>>not we are the only connection? Createdb is hardly a time critical 
>>operation, and taking a few seconds extra in the worst case wouldn't 
>>matter that much, would it?
>>    
>>
>
>Hmm, that might help, though as you say it's only a 90% solution.
>
>It occurs to me that it wouldn't really be that hard to develop a
>locking-based solution, if we consider that the purpose of the lock
>is to keep new connections out of the template database.  Let's assume
>we define a lock tag that represents a whole database (dbId = db's OID,
>relId = zero would probably do).  Modify the backend so that every
>session takes a share-lock on this tag during its startup sequence
>(early in ReverifyMyDatabase() would likely be a good place).  The lock
>does not need to be held over the life of the session, just acquired
>before any database write can possibly occur in the session.  That means
>we don't need any complicated new support in the lock manager.
>
>Now, we can make CREATE DATABASE (and DROP DATABASE) acquire exclusive
>lock on this lock before they look for extant sessions attached to the
>target database.  That prevents any new sessions from getting in.  In
>the CREATE case, incoming sessions for the template database are simply
>delayed until the CREATE completes.  In the DROP case, incoming sessions
>for the doomed database are guaranteed to notice that their database is
>gone, because they won't be able to look for it in pg_database until
>after the DROP completes.  (I think we currently guarantee the latter
>by means of DROP taking an exclusive lock on pg_database, but a lock
>that didn't lock out unrelated operations would surely be nicer.)
>
>We could combine this with your idea of delaying: after acquiring the
>exclusive lock, CREATE can delay to see if existing sessions connected
>to the template database quit soon.  Since the exclusive lock is
>preventing new sessions from coming in, this delay does not need to be
>very long (probably the average length of a CREATE DATABASE operation
>would be enough).
>
>Thoughts?
>
>  
>

Neat. Took me a few minutes to get my head around how it would work. 
Seems like any success would be guaranteed to be correct, which is a 
definite advance, and only long-lived connections to the template (e.g. 
"psql template1") would cause failures. A delay of around 2 secs seems 
right on my system.

(I played around in my head with other ideas like a read-only connection 
flag and things based on checksumming, but they either seemed likely to 
be error-prone or too expensive.)

cheers

andrew