Greenplum Error: current transaction is aborted, commands ignored until end of transaction block
search cancel

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

book

Article ID: 295170

calendar_today

Updated On: 09-01-2020

Products

VMware Tanzu Greenplum

Issue/Introduction

This article applies to all versions of Greenplum Database (GPDB) and explains how to deal with the error message "current transaction is aborted, commands ignored until end of transaction block".

This means an executed query is under a transaction block. This error is caused by the wrong query being executed or a query containing invalid syntax in the transaction block that is opened.

This error marked the transaction block as invalid. Any further transaction will result in the following error:
ERROR:  current transaction is aborted, commands ignored until end of transaction block
Unfortunately, the only solution is to 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)


Resolution

There is no way to instruct Postgres itself to ignore errors inside of a transaction. However, the ON_ERROR_ROLLBACK feature is available since psql version 8.1.

Enable ON_ERROR_ROLLBACK, psql will issue a SAVEPOINT before every command sent to Postgres. If an error is detected, it will issue a ROLLBACK TO the previous SAVEPOINT. This provides the chance to re-enter the command without issue. If an error is not detected, psql does a RELEASE savepoint behind the scenes.

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)