Transactional DDL in PostgreSQL: Database Comparison

Summary: In this article, we will learn the transactional DDL in PostgreSQL, and differences with other popular databases.

Table of Contents

Transactional DDL

Like several commercial databases, 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:

DROP TABLE IF EXISTS foo;
NOTICE: table "foo" does not exist

BEGIN;
CREATE TABLE foo (bar int);
INSERT INTO foo VALUES (1);
ROLLBACK;

SELECT * FROM foo;
ERROR: relation "foo" does not exist

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 transactions available at all. For InnoDB, the server has an implicit commit that occurs even if the normal auto-commit behavior is turned off, DDL commands cause implicit commit of the current transaction.

set autocommit = 0;

drop table foo;

create table foo (bar int) engine=InnoDB;

insert into foo values (1);

rollback;

select * from foo;
Empty set (0.00 sec)

Oracle

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.

Oracle Database issues an implicit COMMIT under the following circumstances:

• Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error.
• After any data definition language (DDL) statement that completes without an error.

Let’s create a table in Oracle and insert a row:

-- Create a table and insert a row
CREATE TABLE states
(
    abbr CHAR(2),
    name VARCHAR2(90)
);

-- Transaction will be in progress after this insert
INSERT  INTO states VALUES ('CA', 'California');

Now let’s create another table and perform ROLLBACK operation:

-- Create another table table and insert a row
CREATE TABLE cities
(
    name VARCHAR2(90),
    state CHAR(2)
);

INSERT INTO cities VALUES ('San Francisco', 'CA');

ROLLBACK;

You can see that even after ROLLBACK, the table states and row in this table exist because CREATE TABLE cities statement committed the transaction.

Table cities also exists, but the inserted row was rolled back:

-- Table states exists and contains 1 row
SELECT COUNT(*) FROM states;
-- Result: 1

-- Table cities also exists, but the inserted row was rolled back
SELECT COUNT(*) FROM cities;
-- Result: 0

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. If you’re running SQL Server 2005 or greater and running in a snapshot isolation mode (optimistic), there are restrictions to what DDL is supported in an explicit transaction: SQL Server snapshot isolation transactional DDL constraints.

Let’s start a transaction, create a table in SQL Server and insert a row:

-- Start a transaction explicitly
BEGIN TRANSACTION;

-- Create a table and insert a row
CREATE TABLE states
(
    abbr CHAR(2),
    name VARCHAR(90)
);

INSERT  INTO states VALUES ('CA', 'California');
-- 1 row(s) affected

Now let’s create another table, insert a row and perform ROLLBACK operation:

-- Create another table table and insert a row
CREATE TABLE cities
(
    name VARCHAR(90),
    state CHAR(2)
);

INSERT INTO cities VALUES ('San Francisco', 'CA');
-- 1 row(s) affected

ROLLBACK;

You can see that after ROLLBACK, none of the tables exists:

SELECT COUNT(*) FROM states;
-- Msg 208, Level 16, State 1, Line 1
-- Invalid object name 'states'.

SELECT COUNT(*) FROM cities;
-- Msg 208, Level 16, State 1, Line 1
-- Invalid object name 'cities'.