Pivotal Knowledge Base

Follow

How to Overcome the Error "current transaction is aborted, commands ignored until end of transaction block"

Environment

Pivotal Greenplum Database (GPDB) all versions

Purpose

This article describes how to overcome the Error "current transaction is aborted, commands ignored until end of transaction block".

Cause

  • An 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
  • This 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)

Procedure

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