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;
(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;
(0 rows)
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;
(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;
(3 rows)
