Here's some comments from a naive viewpoint (moving from InterBase to
PostgreSQL):
Tom Lane wrote:
> So someone could probably cobble together a real solution consisting of
> locking the table and renaming the files to unique temp names at DROP
> time, then either completing the drop and unlinking the files at commit
> time, or re-renaming them at abort.
Why should all of this renaming stuff be necessary? I would expect all
entities CREATEd in a transaction to live entirely in cache (or a temp file),
and all DROPped entities to remain where they are until COMMIT time, at which
point DROPs should unlink and then CREATEs should create. Is this too hard?
> In short, a lot of work for a very marginal feature. How many other
> DBMSes permit DROP TABLE to be rolled back? How many users care?
Sybase documentation explicitly allows most forms of CREATE and DROP in
transactions. InterBase (which uses a versioning system much like
PostgreSQL) definitely handles CREATE/DROP in transactions correctly, but you
can't access a newly created table until after COMMIT.
What happens, in the current system if you want to make metadata changes in a
transaction and you make a typo that requires ABORTing the changes? If you
have to make all metadata changes outside of transactions, you lose safety at
the most fragile and critical level.