Re: Use of 'cp -r' in CREATE DATABASE

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>>Longer term, a robust mechanism for DB level locks would probably be 
>>preferable, I guess, so I'm not sure if my idea is worth doing in the 
>>mean time. Presumably it hasn't caused much of a problem up to now, 
>>since most people are not likely to monkey with their template dbs at 
>>the same time as trying to create dbs based on them.
>>    
>>
>
>Actually, we do get regular complaints about CREATE DATABASE failing
>because template1 is busy, and that's not because the users are actually
>*doing* anything to template1, it's just because template1 is the
>default database-to-connect-to for a whole lot of operations like
>createuser, createdb itself, psql -l, yadda yadda, and so other backends
>tend to be transiently connected to template1 even though they have no
>intention of doing anything to that database in particular.
>
>A quick-and-dirty solution would be to make another template database,
>so that the default-connection-target could be different from the
>default-copy-source, but that would cost a fair amount of disk space
>(compared to a minimal installation) and create lots of backwards
>compatibility issues too.
>
>I'd like to see a locking-type solution myself, but I'm not sure what
>the semantics look like.  For things like createuser we'd need to invent
>a lock type that says "I'm connected to this database but I only plan to
>modify global tables", or something like that.  Messy.
>
>  
>

Right. So we have 2 problems - we catch too many cases, and we don't 
catch all the cases that matter.

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? This wouldn't be a 100% solution but I 
suspect it would be a 99% solution for the spurious failures, and my 
mtime suggestion could take care of the case where the template db is 
changed after the connection check is performed.

cheers

andrew