Pivotal Knowledge Base

Follow

HowTo - Overcome Error "current transaction is aborted, commands ignored until end of transaction block"

Problem

  • Executed query under a transaction block
  • Executed a wrong query / or query with Invalid syntax in the transaction block that was open and it error'ed out
  • These error marked the transaction block as invalid
  • And now any further transaction will result in the error
ERROR:  current transaction is aborted, commands ignored until end of transaction block

This is a expected behavior, but it can be very annoying if it happens when you are in the middle of a large transaction and mistype something. At that point, the only thing you can do is rollback the transaction and lose all of your work.

For example

gpadmin=# BEGIN;
BEGIN
gpadmin=# insert into Cant_Ignore values(1);
INSERT 0 1
gpadmin=# insert into Cant_Ignore values(2);
INSERT 0 1
gpadmin=# select * from Cant_Ignore;
id
----
  1
  2
(2 rows)

gpadmin=# insert into Cant_Ignore values(ERROR);
ERROR:  column "error" does not exist
LINE 1: insert into Cant_Ignore values(ERROR);
                                       ^
gpadmin=#
gpadmin=# insert into Cant_Ignore values(3);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
gpadmin=# COMMIT;
ROLLBACK
gpadmin=#
gpadmin=# select * from Cant_Ignore;
id
----
(0 rows)

Solution / Workaround

There is no way you can instruct Postgres itself to ignore errors inside of a transaction. The work must be done by a client (such as psql) that can do some voodoo behind the scenes. The ON_ERROR_ROLLBACK feature is available since psql version 8.1.

When ON_ERROR_ROLLBACK is enabled, psql will issue a SAVEPOINT before every command you send to Postgres. If an error is detected, it will then issue a ROLLBACK TO the previous savepoint, which basically rewinds history to the point in time just before you issued the command. Which then gives you a chance to re-enter the command without the mistake. If an error was not detected, psql does a RELEASE savepoint behind the scenes, as there is no longer any reason to keep the savepoint around.

gpadmin=# \set ON_ERROR_ROLLBACK interactive
gpadmin=# BEGIN;
BEGIN
gpadmin=# insert into Cant_Ignore values(1);
INSERT 0 1
gpadmin=# insert into Cant_Ignore values(2);
INSERT 0 1
gpadmin=# select * from Cant_Ignore;
id
----
  2
  1
(2 rows)

gpadmin=# insert into Cant_Ignore values(ERROR);
ERROR:  column "error" does not exist
LINE 1: insert into Cant_Ignore values(ERROR);
                                       ^
gpadmin=#
gpadmin=# insert into Cant_Ignore values(3);
INSERT 0 1
gpadmin=# COMMIT;
COMMIT
gpadmin=# select * from Cant_Ignore;
id
----
  1
  2
  3
(3 rows)

Comments

Powered by Zendesk