Alban Hertroys <haramrae@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
