Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I ask Postgresql to ignore errors within a transaction

I use Postgresql with the PostGIS extensions for ad-hoc spatial analysis. I generally construct and issue SQL queries by hand from within psql. I always wrap an analysis session within a transaction, so if I issue a destructive query I can roll it back.

However, when I issue a query that contains an error, it cancels the transaction. Any further queries elicit the following warning:

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

Is there a way I can turn this behaviour off? It is tiresome to rollback the transaction and rerun previous queries every time I make a typo.

like image 743
fmark Avatar asked Apr 30 '10 02:04

fmark


People also ask

How do I negate in PostgreSQL?

The PostgreSQL NOT condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

Is Dirty read possible in Postgres?

Dirty read doesn't occur in PostgreSQL even the transaction isolation level is READ UNCOMMITTED . READ UNCOMMITTED has the same characteristics of READ COMMITTED in PostgreSQL different from other databases so in short, READ UNCOMMITTED and READ COMMITTED are the same in PostgreSQL.

Which are the commands used to control transactions in PostgreSQL?

In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands.


1 Answers

(UPDATE: No need to to this by hand, I asked in the postgresql mailing lists, and it turned that this behaviour is already implemented, by the ON_ERROR_ROLLBACK set in the psql client)

To elaborate on Simon's answer (+1) , in your scenario you could rutinarily add a savepoint after each interactive query, always with the same name (it ovewrites the previous if the query is succesfull). In the case of error, you go back to the last saved one and continue from there.

An example of this working pattern:

db=# select * from test_gral ;
 i |  t   |  n
---+------+------
 1 | text | 10.0
(1 row)

db=# begin;
BEGIN
db=#  insert into test_gral values (2,'xx',20); savepoint sp;
INSERT 0 1
SAVEPOINT
db=#  insert into test_gral values (3,'xx',30); savepoint sp;
INSERT 0 1
SAVEPOINT
db=#  insert into test_gralxx values (4,'xx',40); savepoint sp;
ERROR:  relation "test_gralxx" does not exist
LINE 1: insert into test_gralxx values (4,'xx',40);
                    ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
db=# ROLLBACK TO SAVEPOINT sp;
ROLLBACK
db=#  insert into test_gral values (4,'xx',40); savepoint sp;
INSERT 0 1
SAVEPOINT
db=# commit;
COMMIT
db=# select * from test_gral ;
 i |  t   |  n
---+------+------
 1 | text | 10.0
 2 | xx   |   20
 3 | xx   |   30
 4 | xx   |   40
(4 rows)
like image 193
leonbloy Avatar answered Sep 18 '22 13:09

leonbloy