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?
Let us assume you are deploying software – all you do is adding three tables.
You might come up with a script:
|
1 2 3 |
CREATE TABLE a (aid int4); CREATE TABBLE b (bid int4); CREATE TABLE c (cid int4); |
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.
In this example we will use a file called START.sql to deploy:
|
1 2 3 4 5 |
[hs@paula tmp]$ cat START.sql BEGIN; \i module_1.sql ROLLBACK; |
We can include additional modules here:
|
1 2 3 4 |
[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:
|
1 2 3 4 5 6 7 8 |
[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.
And yes, there is a thing called CREATE EXTENSION – we also suggest taking a look at that one.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
