CODEWITHSUNDEEP
postgresqltransactionspsql
mbork
mbork

Reputation: 584

How to automatically rollback a transaction in psql's interactive mode?

Let us assume the following contents of the file erroneous-query.sql:

BEGIN;
SELECT 1/0;
COMMIT;

When I say \i erroneous-query.sql in psql's interactive mode, I get this:

BEGIN
psql:erroneous-query.sql:2: ERROR:  division by zero

but I need to say ROLLBACK; manually - otherwise, the transaction is hanging (and blocking everything else).

How can I configure psql to rollback this transaction automatically for me? I tried \set ON_ERROR_ROLLBACK off, but to no avail.

Upvotes: 0

Views: 1619

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246473

There is no way to make psql issue a ROLLBACK automatically.

But an aborted transaction (a transaction that had an error and has no savepoints) will release all locks immediately, so it will never block anybody else.

The remaining statements in the transaction will be done very quickly, each of them will cause

ERROR:  current transaction is aborted, commands ignored until end of transaction block

Doing what you propose would actually be very dangerous: that way part of a transaction (the statements after the error) would be executed in different transactions, and the atomicity of the whole transaction would be lost.

Upvotes: 1

Related Questions