Transactional DDL in PostgreSQL: A Competitive Analysis

From PostgreSQL Wiki

Contents 1 Transactional DDL 2 PostgreSQL 3 MySQL 4 Oracle 5 Informix

Transactional DDL

One of the more advanced features of PostgreSQL compared to almost all its competitors is how well it handles transaction ROLLBACK via its Write-Ahead Log design. That supports backing out even large changes to DDL like table creation; you can't recover from an add/drop on a database or tablespace, but most other 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 foo;
ERROR: table "foo" does not exist
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.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(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.

Oracle

It's not the case that MySQL is behind the industry norm here, it's that PostgreSQL is ahead of it. This same behavior is true of Oracle. 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."

Informix

Informix is one of the few commercial databases that does support 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