Alban Hertroys <haramrae@gmail.com> writes:
>> I've read that postgres uses MVCC for transactions, and that it creates
>> snapshots of the database for each transaction. �Couldn't the create
>> database command just use that snapshot?
> Database creation cannot be done inside a transaction (one of the few
> DDL statements that can't), so no.
It's a little more complicated than that. The real answer is that
CREATE DATABASE works by doing a filesystem copy of the source database,
so if there are any concurrent changes going on, it can't get a
consistent snapshot of that database's state.
It's interesting to think about ways that that restriction might be
weakened, but I don't see any way to do it that wouldn't involve taking
some type of lock on each table in the source database --- and, at some
point, locking out the ability to create any new tables there too.
That would be messy, deadlock-prone, and probably still pretty
restrictive for transactions in the source database. Another issue,
if the locks in question don't prohibit writes, is that as soon as
you've cloned a given table any WAL-logged actions issued against that
table would have to be duplicated for the clone in the new database;
something the transactions in the source database couldn't be expected
to know that they have to do, since after all the new database doesn't
exist yet from their perspective.
On the whole, even if it's possible at all, the work-to-payoff ratio
doesn't look very attractive.
regards, tom lane