Transactional DDLs

PostgreSQL has a very nice feature that is unfortunately not present in many commercial database systems. In PostgreSQL, it is possible to run DDLs (commands that change the data's structure) inside a transaction block. In a typical commercial system, a DDL will implicitly commit the current transaction. This does not occur in PostgreSQL.

Apart from some minor exceptions (DROP DATABASE, CREATE TABLESPACE, DROP TABLESPACE, and so on), all DDLs in PostgreSQL are transactional, which is a huge advantage and a real benefit to end users.

Here is an example:

test=# \d
No relations found. 
test=# BEGIN; 
BEGIN 
test=# CREATE TABLE t_test (id int); 
CREATE TABLE 
test=# ALTER TABLE t_test ALTER COLUMN id TYPE int8; 
ALTER TABLE 
test=# \d t_test 
    Table "public.t_test" 
 Column |  Type  | Modifiers  
--------+--------+----------- 
 id     | bigint |  
 
test=# ROLLBACK; 
ROLLBACK 
test=# \d 
No relations found.

In this example, a table has been created and modified, and the entire transaction has been aborted. As you can see, there is no implicit COMMIT command or any other strange behavior. PostgreSQL simply acts as expected.

Transactional DDLs are especially important if you want to deploy software. Just imagine running a content management system (CMS). If a new version is released, you'll want to upgrade. Running the old version would still be OK; running the new version would also be OK, but you really don't want a mixture of old and new. Therefore, deploying an upgrade in a single transaction is highly beneficial as it upgrades an atomic operation.