Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

I am working on a program that issues DDL. I would like to know whet开发者_开发技巧her CREATE TABLE and similar DDL can be rolled back in

• Postgres
• MySQL
• SQLite
• et al

Describe how each database handles transactions with DDL.

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL's perspective.

Is DDL transactional according to this document?

• PostgreSQL - yes
• MySQL - no; DDL causes an implicit commit
• Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
• Older versions of Oracle - no; DDL causes an implicit commit
• SQL Server - yes
• Sybase Adaptive Server - yes
• DB2 - yes
• Informix - yes
• Firebird (Interbase) - yes

SQLite also appears to have transactional DDL as well. I was able to ROLLBACK a CREATE TABLE statement in SQLite. Its CREATE TABLE documentation does not mention any special transactional 'gotchas'.

PostgreSQL has transactional DDL for most database objects (certainly tables, indices etc but not databases, users). However practically any DDL will get an ACCESS EXCLUSIVE lock on the target object, making it completely inaccessible until the DDL transaction finishes. Also, not all situations are quite handled- for example, if you try to select from table foo while another transaction is dropping it and creating a replacement table foo, then the blocked transaction will finally receive an error rather than finding the new foo table. (Edit: this was fixed in or before PostgreSQL 9.3)

CREATE INDEX ... CONCURRENTLY is exceptional, it uses three transactions to add an index to a table while allowing concurrent updates, so it cannot itself be performed in a transaction.

Also the database maintenance command VACUUM cannot be used in a transaction.

Can't be done with MySQL it seems, very dumb, but true... (as per the accepted answer)

"The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction."

https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Tried a few different ways and it simply won't roll back..

Work around is to simply set a failure flag and do "drop table tblname" if one of the queries failed..

Looks like the other answers are pretty outdated.

As of 2019:

• Postgres has supported transactional DDL for many releases.
• SQLite has supported transactional DDL for many releases.
• MySQL has supported Atomic DDL since 8.0 (which was released in 2018).

While it is not strictly speaking a "rollback", in Oracle the FLASHBACK command can be used to undo these types of changes, if the database has been configured to support it.