CYBERTEC Logo

Transactional DDLs – a message to non-PostgreSQL users

06.2013 / Category: / Tags: |

Sometimes it is astonishing to see which basic functionality commercial vendors lack. One of the things that strikes me most is the fact that many expensive relational database systems are simply not capable of handling transactional DDLs (such as CREATE TABLE, CREATE INDEX, and so on).

How can you ever deploy software in a reasonable way, if CREATE TABLE will implicitly commit a transaction? How can you ever deploy software if your scripts cannot be atomic? How can you ever handle errors that way? 

The scenario 

Let us assume you are deploying software – all you do is adding three tables.

You might come up with a script:

This script contains a little mistake. Who is capable of writing long scripts without a single mistake? I am clearly not able to do that. So, if you run that script you need a “fix script” to clean out the problems of the first one. Who says that the cleanout script will be any better than the script you are trying to write? Deploying software in a non-transactional way is definitely not the way to go.

Using transactions includes:

In this example we will use a file called START.sql to deploy:

[hs@paula tmp]$ cat START.sql

BEGIN;

 i module_1.sql

 ROLLBACK;

We can include additional modules here:

 [hs@paula tmp]$ cat module_1.sql

CREATE TABLE a (aid int4);

CREATE TABBLE b (bid int4);

CREATE TABLE c (cid int4);

Once we have written the code we can test it:

 [hs@paula tmp]$ psql test < START.sql

BEGIN

CREATE TABLE

psql:module_1.sql:2: ERROR: syntax error at or near "TABBLE"

LINE 1: CREATE TABBLE b (bid int4);

^

psql:module_1.sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK

The important thing here is that there are NO LEFTOVERs. Nothing will stay behind because the transaction will be rolled back. It means that we can already test our stuff without having to persist it. We can nicely fix the script and execute it cleanly then.

 PostgreSQL: CREATE EXTENSION

And yes, there is a thing called CREATE EXTENSION – we also suggest taking a look at that one.

Comments are closed.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram