The Crystal Programming Language Forum

DB Connection Pool + retry_attempts (from Docs) not working?

Hello, trying DB pool example from official docs:
https://crystal-lang.org/reference/database/connection_pool.html

This code:

DB.open("postgres://postgres@dbdocker:15432/my_database?retry_attempts=10&retry_delay=3") do |db|
  loop do
    puts "#{Time.now} #{db.scalar("select count(*) from sometable")}"
    sleep 3.seconds
  end
end

But when I stop docker container with Postgres database, exception occurs immediately without retrying:

Unhandled exception: Error writing to socket: Broken pipe (Errno)
  from /usr/local/lib/crystal/socket.cr:79:13 in 'unbuffered_write'
  from /usr/local/lib/crystal/io/buffered.cr:179:5 in 'flush'
  from /usr/local/lib/crystal/io/buffered.cr:187:5 in 'close'
  from lib/pg/src/pq/connection.cr:79:9 in 'close'
  from lib/pg/src/pg/connection.cr:59:7 in 'do_close'
  from lib/db/src/db/disposable.cr:11:7 in 'close'
  from lib/db/src/db/pool_statement.cr:98:11 in 'scalar'
  from lib/db/src/db/query_methods.cr:272:7 in 'scalar'
  from src/main.cr:72:27 in '__crystal_main'
  from /usr/local/lib/crystal/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/local/lib/crystal/crystal/main.cr:86:7 in 'main'
  from /usr/local/lib/crystal/crystal/main.cr:106:3 in 'main'
  from _start
  from ???

Whats wrong?

Hi @pfischer,

I couldn’t repro. Con you clarify the shards version and postgres just in case?

Despite that, I did a code review and found that the following would be a proper connection close.

module PG
  class Connection
    protected def do_close
      super

      begin
        @connection.close
      rescue
      end
    end
  end
end

Could you check if adding that to your script fixes the issue on your side?

After some thinking, it maybe seems OK, the code that reads the resultset just crashed in the middle of reading (when the database was closed) - so, Exception (Broken pipe) is probably OK.

This code is better:

DB.open("postgres://postgres@dbdocker:15432/my_database?retry_attempts=10&retry_delay=3") do |db|
  loop do
    begin
     puts "#{Time.now} #{db.scalar("select count(*) from sometable")}"
    rescue ex
      # do something with exception
    end
    sleep 3.seconds
  end
end

So in the case, for example, a REST API Server, some API calls fails (db breakage in the middle of resultset reading), some API calls fails after retry_attempts, and when the database starts again, the server will continue to serve responses.

There is not much to do if the failure occurs in the middle of the query probably. That’s true.

I’m intrigued, you hit the errors consistently? Because since you were querying using db.scalar the connection should have been used for very little time.

Yes, Broken pipe exceptions are quite consistent on my side.