# DB Connection Pool + retry\_attempts (from Docs) not working?

**URL:** <https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809>\
**Category:** Help & Support\
**Created:** [May 31, 2019, 10:12am UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809 "2019-05-31T10:12:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pfischer](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/pfischer/32/330_2.png) [@pfischer](https://forum.crystal-lang.org/u/pfischer)\
**Post date:** [May 31, 2019, 10:12am UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809/1 "2019-05-31T10:12:23Z")

</div>

Hello, trying DB pool example from official docs:  
[https://crystal-lang.org/reference/database/connection\_pool.html](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?

---

<div class="post-metadata">

**Author:** ![bcardiff](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/bcardiff/32/3_2.png) [@bcardiff](https://forum.crystal-lang.org/u/bcardiff)\
**Post date:** [May 31, 2019, 1:02pm UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809/2 "2019-05-31T13:02:18Z")

</div>

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.

```crystal
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?

---

<div class="post-metadata">

**Author:** ![pfischer](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/pfischer/32/330_2.png) [@pfischer](https://forum.crystal-lang.org/u/pfischer)\
**Post date:** [May 31, 2019, 7:02pm UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809/3 "2019-05-31T19:02:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bcardiff](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/bcardiff/32/3_2.png) [@bcardiff](https://forum.crystal-lang.org/u/bcardiff)\
**Post date:** [May 31, 2019, 7:19pm UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809/4 "2019-05-31T19:19:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pfischer](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/pfischer/32/330_2.png) [@pfischer](https://forum.crystal-lang.org/u/pfischer)\
**Post date:** [May 31, 2019, 7:28pm UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809/5 "2019-05-31T19:28:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![carcinocron](https://yyz2.discourse-cdn.com/flex036/user_avatar/forum.crystal-lang.org/carcinocron/32/1022_2.png) [@carcinocron](https://forum.crystal-lang.org/u/carcinocron)\
**Post date:** [April 6, 2023, 10:20pm UTC](https://forum.crystal-lang.org/t/db-connection-pool-retry-attempts-from-docs-not-working/809/6 "2023-04-06T22:20:43Z")

</div>

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:

```auto
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.