Is ROLLBACK; on error automatic in a basic PostgreSQL DDL script (BEGIN; <STATEMENTS;> COMMIT;)?

Question

I want to run numerous statements in an atomic transaction on a Postgres database. Is it sufficient to put BEGIN; at the start of the script and COMMIT; at the end of the script? I want to ensure that if an error occurs during the transaction, it's automatically rolled back.

For example, I have the script test.sql:

BEGIN;
SELECT
 *
FROM
 intentional_error;
CREATE TABLE faketable (fakecolumn TIMESTAMP);
COMMIT;

intentional_error is not a real table in my schema, so I expect the first statement in the transaction to error. When it errors, I'm wondering if faketable will be created.

From my research, I can't find Postgres docs that clearly state that a ROLLBACK; is automatic in this scenario. There is a Stack Exchange answer that states that ROLLBACK; is automatic, but I would love to see a test or something from the Postgres docs.

• Laurenz Albe: If you want it documented, write to the pgsql-docs mailing list. Ideally, submit a patch.

Answer 1

I tested this question on a Postgres database (version 12.44). I created the file test.sql (same as in the question):

BEGIN;
SELECT
 *
FROM
 intentional_error;
CREATE TABLE faketable (fakecolumn TIMESTAMP);
COMMIT;

This was the output from CLI.

-d postgres -f test.sql -W
Password: 
BEGIN
psql:test.sql:5: ERROR: relation "intentional_error" does not exist
LINE 4: intentional_error;
 ^
psql:test.sql:6: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

Then I double-checked that the table wasn't created.

$ psql -h my-host -U postgres -d postgres -W
Password: 
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1), server 12.14)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
postgres=> \d faketable
Did not find any relation named "faketable".

In conclusion, putting BEGIN; at the start of the script and COMMIT; at the end of the script is sufficient to ROLLBACK; the transaction in the case that one of the statements errors.