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

Tom Lane wrote:

>Andrew Dunstan <andrew(at)dunslane(dot)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