How do you access database error information when using Rails and Postgres

Question

I am using find_by_sql to connect to a Postgres database and execute a database function. The database function executes a number of SQL statements and raises exceptions as required.

How do I trap the error code and error message raised by the Postgres function in Rails?

def self.validate_email(systemuserid, emailaddress)
  begin
    result = (self.find_by_sql(["SELECT fn_systemuser_validate_email(?, ?) AS returncode", 
                                systemuserid, emailaddress])).first
  rescue => err
    # I want to get access to the error code and error message here and act accordingly
    # errno = ??
    # errmsg = ??
    if errno == 10000
    end
  end
  return result[:returncode]
end

I started by trying to find this information in the connection object - no such luck.

Any help much appreciated.

Answer 1

Currently active record replaces the original error with an internal one without passing on the original with the new error. I cant understand why any one would want this.

So the only solution right now is to monkey patch ;)

module ActiveRecord
  module ConnectionAdapters
    class AbstractAdapter
      def translate_exception(e, message)
        ActiveRecord::WrappedDatabaseException.new(message,e)
      end

      # Replaces
      # def translate_exception(e, message)
      #   # override in derived class
      #   ActiveRecord::StatementInvalid.new(message)
      # end
    end
  end
end

Now you can get the original_exception.

def self.validate_email(systemuserid, emailaddress)
  begin 
    result = (self.find_by_sql(["SELECT fn_systemuser_validate_email(?, ?) AS returncode", systemuserid, emailaddress])).first
  rescue ActiveRecord::WrappedDatabaseException => e

    pgerror = e.original_exception

    # Exact api depends on PG version, check the docs for your version.
    puts "Doing my stuff: #{pgerror.result.result_error_message}"

  end
end

This works with pg version 0.11 and Rails 3.0.9. Will probably work with later versions.

Answer 2

I let this one go for a while, (9 months!) but picked it up again due to a new impetus.

I used the monkey patch suggested by Darwin (sorry that the pull request didnt get the vote) and have then discovered that the code I need (with reference to http://deveiate.org/code/pg/PG/Result.html) is as follows:

rescue ActiveRecord::WrappedDatabaseException => e

  pgerror = e.original_exception
  sqlstate = pgerror.result.error_field(PG::Result::PG_DIAG_SQLSTATE )
end