Transactional DDL in PostgreSQL: A Competitive Analysis
From PostgreSQL Wiki
Contents |
Transactional DDL
In addition to 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 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 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.
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.
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.
