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

Help & Support
1

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?

2

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?

3

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.

4

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.

5

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

6

This seemed to happen 100% of the time for me. Not instantly, but a long running script with sleep will eventually raise. I’m not sure what I’m suppose to do about it, but I think this change fixed it:

DB.open("postgres://postgres@dbdocker:15432/my_database?retry_attempts=10&retry_delay=3") do |db|
  loop do
    db.using_connection do |cnn|
      # not sure if cnn or db would be the right variable here
      puts "#{Time.now} #{cnn.scalar("select count(*) from sometable")}"
      sleep 3.seconds
    end
  end
end

This is the first search result that seems actually related, so I’ll leave this here. It might have already had something to do with me trying to use prepared_statement = db.build("select * from contacts where id=?") without db.using_connection. Either way, I wish the docs explained more or provided better examples for getting started with new projects.