Postgresql – Basic transactional DDL script in PostgreSQL

ddlplpgsqlpostgresqltransaction

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?

Best Answer

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;

You cannot start, commit or roll back transactions inside plpgsql at all, since a plpgsql block is always run in the context of an outer transaction automatically.

Don't confuse SQL commands for transaction management with elements of a plpgsql 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 not available inside plpgsql code and there are no plpgsql commands outside plpgsql 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 which is never rolled back.
You don't need any of this for your demonstrated code.

Related Question