Postgres and the Rails Sandbox

This may be an esoteric problem, but if you've encountered it, you can probably empathize with how obnoxious it is. The gist of it is that Postgres aborts a transaction as soon as an error occurs, e.g. trying to select a column which does not exist. If you're in a sandboxed Rails console, you're basically hosed when you encounter an error like this since sandbox mode is literally just a database transaction which is rolled back upon exiting; subsequent queries will fail until the transaction is rolled back.

test=# begin; BEGIN test=# select blah from users; ERROR: column "blah" does not exist LINE 1: select blah from users; ^ test=# select id from users; ERROR: current transaction is aborted, commands ignored until end of transaction block

A few months ago, I wrote this post demonstrating how to default the Rails console into sandbox mode. This is a pretty nice feature for production console access, but it becomes obscenely annoying as soon as you encounter this issue; now, every time you fat-finger a query, you're pretty much forced to exit and restart the console.

If you use pry-rails, there is a pretty simple solution for this, namely a custom exception handler. If you don't already have one set up, create a .pryrc file and add this snippet to it:

Pry.config.exception_handler = proc do |output, exception, _pry_|
  output.puts "#{exception.class}: #{exception.message}"
  output.puts exception.backtrace.first

  if exception.instance_of?(ActiveRecord::StatementInvalid) && exception.original_exception.is_a?(PG::Error)
    output.puts 'Rolling back transaction and starting a new one!'
    connection = ActiveRecord::Base.connection
    connection.rollback_db_transaction
    connection.begin_db_transaction
  end
end

What this does is observe errors raised by console statements, and upon seeing an ActiveRecord::StatementInvalid of the Postgres variety, rolls back the current transaction and starts a new one:

Loading development environment in sandbox (Rails 4.2.7.1)
Any modifications you make will be rolled back on exit

Frame number: 0/20
[1] pry(main)> User.pluck(:blah)
   (1.0ms)  SELECT blah FROM "users"
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "blah" does not exist
LINE 1: SELECT blah FROM "users"
               ^
: SELECT blah FROM "users"
/Users/travis/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `async_exec'
Rolling back transaction and starting a new one!
   (0.3ms)  ROLLBACK
   (0.2ms)  BEGIN

This makes me feel kind of dirty, but hey...I already feel dirty using a giant transaction to protect a database from a day-dreaming developer. Unfortunately, this code cannot directly detect PG errors since those are swallowed by ActiveRecord. I bet you could monkey-patch your way to another solution, if you're into that sort of thing.

Update: turns out you can access the original exception via an accessor on the ActiveRecord::StatementInvalid exception. The code sample has been updated to reflect this.