0

In my code I open DB at start of program and pass db variable to other methods. I think it's stupid and not right. But what should I do? Should I open db connection in each method? But this way also doesn't look right... And I have a lot of errors: DB::ConnectionRefused, DB::PoolTimeout, DB::PoolRetryAttemptsExceeded So it something wrong with my code.

def main_meth
db = DB.open("postgres://blabla@localhost:5432/bla?retry_attempts=8&retry_delay=3&max_pool_size=50&max_idle_pool_size=10&checkout_timeout=0.1") # there is always same story with or without params.
begin

db.scalar("")
...
another_meth(params, db)

channel = Channel(Nil).new(20)
    groups.each do |group|
      spawn one_more_meth(group, channel, db)
    end
    groups.size.times { channel.receive }

 ensure
      db.close
    end
end

def another_meth(p, db)
deeper_meth(db)
end

def one_more_meth(group, channel, db)
...
db.query_all
...
channel.send(nil)
end
nobilik
  • 736
  • 9
  • 29
  • It should be correct to pass a single DB around the whole program: it handles pooling internally. Do any queries work at all or do the errors only happen when doing lots of queries? If not, does it work from another language, using the same connection URL? – Stephie Mar 16 '17 at 18:08
  • It happens with a lot of queries. Yes, there are 10 independent programs on crystal + 1 rails app. But I have 2000 connections max set in my pg – nobilik Mar 16 '17 at 18:14
  • But on my local machine I have same errors with only one app running. – nobilik Mar 16 '17 at 18:22
  • But it doesn't happen on *all* queries from crystal, am I correct? – Stephie Mar 16 '17 at 18:24
  • Yes, you are. It looks like connections pool released connections too slow. – nobilik Mar 16 '17 at 18:26
  • I think this is a bug, or something regarding more investigation. I'd suggest making an issue here: https://github.com/crystal-lang/crystal-db/issues – Stephie Mar 16 '17 at 18:37
  • I have suggestion that on my local computer it can be because hdd is too slow ( my new comp is broken). And because of it I open and close DB in each method for connection releasing. But on production it can generate "connection refused". – nobilik Mar 17 '17 at 02:46
  • I ran into a similar issue and I found is that when doing `db.query` you have to make sure to either use a block: `db.query("") do |rs|; end` or close the result set: `rs = db.query("");rs.close` – fridgerator Mar 18 '17 at 20:20
  • @fridgerator Thanks for advise. But I don't use db.query this way. I use db.scalar, db.exec, db.query_all and Class.from_rs(db.query ""). As far as I understand no one from these queries should be closed manually. – nobilik Mar 19 '17 at 08:32
  • `Class.from_rs(db.query "")` might not close the result set. You could try saving the query to a variable, and after `from_rs` closing it: `rs = db.query("");Class.from_rs(rs);rs.close` – fridgerator Mar 20 '17 at 04:10
  • @fridgerator It looks like you are right! Thanks. You are welcome to add an answer, I'll accept it. – nobilik Mar 20 '17 at 04:35

1 Answers1

1

I ran into a similar issue and I found is that when doing db.query you have to make sure to either:

save the result sets to a variable and after usage close them

rs = db.query("")
Class.from_rs(rs)
rs.close

OR use a block

db.query("") do |rs|
    Class.from_rs(rs)
end
fridgerator
  • 323
  • 7
  • 17