Continuing a PostGIS transaction after an error

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 for this tip.


  1. 1 Always use transactions when doing analysis in PostGIS « Opaque Overlay

    […] UPDATE:In order to avoid rolling back transactions every time you make a typo inside them, set ON_ERROR_ROLLBACK to interactive. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: