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

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.

regards, tom lane