Transactional DDL in PostgreSQL: A Competitive Analysis

== Transactional DDL ==
Like several of its commercial competitors, one of the more advanced features of PostgreSQL is its ability to perform transactional DDL via its Write-Ahead Log 
design. This design supports backing out even large changes to
DDL, such as table
creation. You can't recover from an add/drop on a database or tablespace, but
all other catalog operations are reversible.
== PostgreSQL ==
Here is an example showing how robust the PostgreSQL design is in this area
(thanks to Nigel McNie for this and the MySQL
example below):
 $ psql mydb
 mydb=# DROP TABLE IF EXISTS foo;
 NOTICE: table "foo" does not exist
 DROP TABLE
 mydb=# BEGIN;
 BEGIN
 mydb=# CREATE TABLE foo (bar int);
 CREATE TABLE
 mydb=# INSERT INTO foo VALUES (1);
 INSERT 0 1
 mydb=# ROLLBACK;
 ROLLBACK
 mydb=# SELECT * FROM foo;
 ERROR: relation "foo" does not exist
 mydb=# SELECT version();
 version
 ----------------------------------------------------------------------
 PostgreSQL 8.3.7 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20081105 (Red Hat 4.3.2-7)
 (1 row)
Experienced PostgreSQL DBA's know to take advantage of this feature to protect
themselves when doing complicated work like schema upgrades. If you put all
such changes into a transaction block, you can make sure they all apply
atomically or not at all. This drastically lowers the possibility that the
database will be corrupted by a typo or other such error in the schema change,
which is particularly important when you're modifying multiple related tables
where a mistake might destroy the relational key.
== MySQL ==
If you're using MySQL instead, DDL and some similar changes cannot be reversed
in such a fashion. If you're using MyISAM, there's no rollback available at
all. With InnoDB, the server has an
implicit commit
that occurs even if the normal auto-commit behavior is turned off. It's
notable that the scope of when you can encounter the implicit commit behavior
has even been expanding during the 5.0 releases; you can draw your own
conclusions about what that says about the commit reliability of the earlier
versions. Here is how that same procedure plays out with a recent MySQL
version:
 mysql> drop table if exists foo;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> begin;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> create table foo (bar int) type=InnoDB;
 Query OK, 0 rows affected, 1 warning (0.03 sec)
 
 mysql> insert into foo values (1);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> rollback;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from foo;
 +------+
 | bar |
 +------+
 | 1 |
 +------+
 1 row in set (0.00 sec)
 
 mysql> select version();
 +--------------------------+
 | version() |
 +--------------------------+
 | 5.0.32-Debian_7etch1-log |
 +--------------------------+
 1 row in set (0.00 sec)
You should also be aware that because of the way they've been implemented,
rollbacks in MySQL execute very slowly compared to the original insertion.
With 5.5 (and autocommit turned off) this is no longer the case:
 mysql> set autocommit = 0;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> drop table foo;
 Query OK, 0 rows affected (0.02 sec)
 
 mysql> create table foo (bar int) engine=InnoDB;
 Query OK, 0 rows affected (0.08 sec)
 
 mysql> insert into foo values (1);
 Query OK, 1 row affected (0.00 sec)
 
 mysql> rollback;
 Query OK, 0 rows affected (0.02 sec)
 
 mysql> select * from foo;
 Empty set (0.00 sec)
 
 mysql> select version();
 +-----------+
 | version() |
 +-----------+
 | 5.5.8 |
 +-----------+
 1 row in set (0.05 sec)
== Oracle ==
As of Oracle Database 11g Release 2, Oracle supports Edition-Based Redefinition, which provides database-wide versioning of schema objects to enable the online testing and upgrade of an application while it is in production. Outside of using that feature or in earlier versions, according to Transaction management "A transaction ends when...a user runs a DDL
statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction
contains any DML statements, Oracle first commits the transaction, and then
runs and commits the DDL statement as a new, single statement transaction."
and "an implicit request occurs after normal termination of an application or
completion of a data definition language (DDL) operation."
== SQL Server ==
SQL Server supports transactional DDL in some cases. If you are running SQL Server in read-committed isolation mode (pessimistic), which is the default mode, you are able to use transactional DDL SQL Server supports transactional DDL. If you're running SQL Server 2005 or greater and running in a snapshot isolation mode (optimistic) there're restrictions to what DDL is supported in an explicit transaction SQL Server snapshot isolation transactional DDL constraints.
"SQL Server does not support versioning of metadata. For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement. These statements are permitted when you are using snapshot isolation within implicit transactions. An implicit transaction, by definition, is a single statement that makes it possible to enforce the semantics of snapshot isolation, even with DDL statements. Violations of this principle can cause error 3961: "Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.""
== Sybase Adaptive Server ==
Sybase Adaptive Server supports transactional DDL.
== DB2 UDB ==
DB2 supports transactional DDL.
== Informix ==
Informix supports transactional DDL. (thanks to Gregory Williamson of Digital Globe 
for this example)
 SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
 Modify the current SQL statements using the SQL editor.
 ----------------------- mydb@myserver ------ Press CTRL-W for Help --------
 drop table foo;
 
 111: ISAM error: no record found. Not in the database.
 
 ===
 BEGIN;
 
 Started transaction.
 
 ===
 create table foo (bar int);
 
 Table created.
 
 ===
 insert into foo values (1);
 
 1 row(s) inserted.
 
 ===
 rollback;
 
 Transaction rolled back.
 
 ===
 select * from foo;
 
 111: ISAM error: no record found. Not in the database.
 
 (exit from dbaccess)
 
 myserver% dbschema -v
 INFORMIX-SQL Version 9.30.UC1
== Firebird (Interbase) ==
Firebird/Interbase also support transactional DDL.
Category:SQL