Continuing a PostGIS transaction after an error

May 1, 2010 in gis, postgis, sql

Yesterday’s post expounded the virtues of doing GIS analysis within SQL transactions. One annoyance of that approach is that whenever an error is made within a transaction, the whole thing must be rolled back and redone. Any further queries won’t be processed within the transaction:

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

Fortunately, this can be avoided, at least within recent versions of psql. All that needs to be done is to set a special variable from within psql and commands can continue to be issued as per normal:

gis=# \set ON_ERROR_ROLLBACK interactive

This variable is unset everytime you close psql. If you want this to be default behaviour within psql, close your psql session and issue the follow command from the shell:

fmark@fmark-laptop:~$ echo '\set ON_ERROR_ROLLBACK interactive' >> ~/.psqlrc

Thanks to the hivemind at stackoverflow.com for this tip.