reconnect_pg_with_connection_pool.rb

## Tested under Jruby and Connection Pool 2.2.1
## jruby 9.0.5.0 (2.2.3) 2016-01-26 7bee00d Java HotSpot(TM) 64-Bit Server VM 25.144-b01 on 1.8.0_144-b01 +jit [darwin-x86_64]
require 'pg'
require 'colorize'
require 'connection_pool'
require 'securerandom'
$db_options = {user: 'postgres', dbname: 'postgres'}
class ScpLookup
  attr_reader :pg_conn, :db_options
  def initialize(db_options)
    @db_options = db_options
    @pg_conn = PG.connect(db_options)
  end

  def reconnect!
    @pg_conn = PG.connect(db_options)
  end

  def self.connect(db_options)
    new(db_options)
  end
end
$pg_conn = ConnectionPool.new(size: 1, timeout: 5) { ScpLookup.connect($db_options) }

Thread.abort_on_exception = true
class Worker
  def start
    puts "starting worker"
    retries = 0
    Thread.current[:id] = SecureRandom.hex
    $pg_conn.with do |lookup|
      begin
        puts ".. #{Thread.current[:id]}"
        puts lookup.to_s[0..30] + "--" + lookup.pg_conn.to_s[0..75]
        result = lookup.pg_conn.exec('select 1 num')
        puts result.first['num']
      rescue Exception => exception
        puts "got exception ...".red()
        lookup.reconnect!
        retry if (retries += 1) < 2
        puts "\ngot error from #{Thread.current[:id]} .... #{exception.message}\n"
      end
    end
  end
end

loop do
  Thread.new { Worker.new.start }
  sleep 0.5
end