Basic transactional DDL script in PostgreSQL

Question

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the whole thing back; otherwise commit. I'm having a hard time getting the syntax right, and I suspect my problems might stem from conflating SQL DDL with PL/pgSQL and/or not grokking the transaction semantics.

Long story short, what's the PL/pgSQL boilerplate for a script to do something like this?

My attempts have gone something like schema.sql here:

BEGIN;

CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL

EXCEPTION WHEN OTHERS THEN ROLLBACK; -- this, I gather, is PL/pgSQL

COMMIT;

\i schema.sql at the psql prompt produces a syntax error at or near EXCEPTION. OK, so EXCEPTION must be PL/pgSQL, and all this needs to go in a PL/pgSQL declaration. Let's try again:

Per the grammar at http://www.postgresql.org/docs/9.3/static/plpgsql-structure.html, this looks like:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

I don't need to name this, it's a one-off provisioning script, not declaring a function I'm going to use again. So skip the declarations and expand statements to what I had before:

BEGIN

    BEGIN;

    CREATE TABLE IF NOT EXISTS blah ( ... ); -- much DDL

    EXCEPTION WHEN OTHERS THEN ROLLBACK; 

    COMMIT;

END;

This blows up in even worse fashion, with syntax errors on BEGIN, EXCEPTION, and then it keeps running the rest of the script anyways, complaining about not being in a transaction.

What's the misunderstanding I have here?

Answer 1

if any errors, roll the whole thing back;

It's simpler than you seem to think. Any exception in a transaction (that is not trapped somehow) triggers a ROLLBACK for the whole transaction automatically. You don't have to do anything extra.

BEGIN;
CREATE TABLE IF NOT EXISTS blah ( ... );
-- much more DDL
COMMIT;

Before Postgres 11, one could not start, commit or roll back transactions inside PL/pgSQL code blocks at all, which is always run in the context of an outer transaction automatically. With the addition of SQL PROCEDURE, COMMIT is allowed now, but still not in functions. See:

• COMMIT works in one plpgsql code block, but not in another?

Don't confuse SQL commands for transaction management with elements of a PL/pgSQL code block. BEGIN is used as keyword in both, that's the only thing in common. That's never ambiguous, because the SQL command is (still!) not available inside PL/pgSQL code and there are no PL/pgSQL commands outside PL/pgSQL code blocks.

An EXCEPTION clause in a plpgsql block is only used to trap errors and do something before or instead of the ROLLBACK. Of course, it only makes sense to do something that ROLLBACK isn't going to undo - like raising a message, signaling or writing to the log.
You don't need any of this for your demonstrated code.

Answer 2

There is no need to use exception and commit as the block itself is transactional. So the following block will not produce anything in case of exception.

DO $$BEGIN create table a(aa int); create table b(aa int);perform pg_sleep(10); create table c as select 1/0 from a; END$$;