15

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.

fmark
  • 57,259
  • 27
  • 100
  • 107

6 Answers6

14

(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)
leonbloy
  • 73,180
  • 20
  • 142
  • 190
  • the ON_ERROR_ROLLBACK variable does exactly what I wanted :) – fmark May 01 '10 at 06:20
  • `ON_ERROR_ROLLBACK` looks like a feature of the psql client though, not postgres itself (so you cannot use this from a database application, only from the command line). – Glyph Mar 02 '12 at 17:17
  • 1
    @Glyph: yes, that's a feature for psql, it's specifically oriented toward interactive use - which was the OP's scenario. It's difficult for me to think of a application scenario when the desired behaviour would make sense. – leonbloy Mar 02 '12 at 18:27
  • @leonbloy Well, any other interactive SQL client out there (which supports postgresql, that is). – Eyal Roth Jan 22 '17 at 11:41
6

Switching that off is not possible however you can use something different. There is something like savepoint:

http://www.postgresql.org/docs/8.4/interactive/sql-savepoint.html

so you can rollback your transaction to some earlier point without rolling black the whole transaction.

Szymon Lipiński
  • 27,098
  • 17
  • 75
  • 77
2

It's possible to write a function that takes a string argument, executes it, and uses an exception clause in order to not abort your transaction, but it's a huge pain to then have to call that function for each statement you wish to execute.

Stephen Denne
  • 36,219
  • 10
  • 45
  • 60
1

No, there is no way to turn this off. An error implicitly aborts the transaction for you, so you must rollback and try again.

Yann Ramin
  • 32,895
  • 3
  • 59
  • 82
0

The simple answer is to run

my_db=> \set ON_ERROR_ROLLBACK interactive

in the interactive session. See also this blog post by its implementor.

Dan
  • 12,409
  • 3
  • 50
  • 87
0

Wanted to add you can ignore errors within postgres itself. I use this for unique key constraints so I don't have to check if something exists first.

INSERT  ...... ON CONFLICT DO NOTHING;
noone392
  • 1,624
  • 3
  • 18
  • 30