0

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.

Trouble Bucket
  • 159
  • 6

1 Answers1

4

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.

Trouble Bucket
  • 159
  • 6