Re: Allowing create database within transaction block

Hari Sankar <harisankars2003(at)gmail(dot)com> writes:
> I wanted to see why we do not allow the following statements to be allowed
> within a transaction block:
> 1. Create database
> 2. Drop Database
> Is there a detailed reasoning behind disallowing the above statements as
> part of the design. Will appreciate it if someone can share on why postgres
> does not allow these statements inside a transaction block.

Mostly it's that create and drop database consist of a filesystem tree
copy and a filesystem recursive delete respectively. So there isn't any
detailed WAL log entry for them, and no way to roll back at transaction
abort.

It might be possible to convert these to roll-back-able operations by
remembering that a recursive delete has to be done during transaction
abort (for the create case) or commit (for the delete case), much as
we do for table create/drop cases. That's a bit scary however,
remembering that it's totally not acceptable to throw any sort of
error at that stage of a transaction commit. Any failure during the
recursive delete would likely end up in leaking a lot of disk space
from files we failed to delete.

Short answer is that it could probably be done if someone wanted to
put enough effort into it, but the cost/benefit ratio hasn't seemed
attractive.

regards, tom lane