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 structure) inside a transaction block. In a typical commercial system, a DDL will implicitly commit the current transaction. Not so 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 plus 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 t_test 
Did not find any relation named "t_test". 

In this example, a table has been created and modified, and the entire transaction is aborted instantly. As you can see, there is no implicit COMMIT 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 CMS. If a new version is released, you'll want to upgrade. Running the old version would still be OK; running the new version is also OK but you really don't want a mixture of old and new. Therefore, deploying an upgrade in a single transaction is definitely highly beneficial as it makes upgrades an atomic operation.