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.
