Re: [Ruby-DBI-devel] Problem with 'too many open cursors'

[Michael N. <mne...@nt...>]

Brian Candler wrote:

>While running some code which does a lot of db.select_one and db.execute
>calls, I was getting Oracle errors saying 'too many open cursors' - although
>I had only a single DBI handle open.
>
That's why you should either explicitly call #finish or use the block 
variants of #execute, #prepare etc..
If you don't do this, well, the GC will collect and close them after 
some time, but it's much faster to open a
lot of handles before the GC gets active.

>Digging around, I found that:
>
>- I should have been using db.do() not db.execute(); the latter returns a
>  sth which I was never using, so Oracle cursors accumulate until garbage
>  collection kicks in
>
As stated above, use the block form of #execute, which frees the 
statement after returning from the block.
If you don't need the result-set, then use #do.

>- db.select_one doesn't seem to call finish() to free up the cursor. I think
>  the attached one-line patch should fix that.
>
It does call #finish, as it uses the block form of #execute.

>However, I'm not sure if there are any knock-on effects. The default
>implementation of DBI::BaseStatement.finish says
>
>  def finish
>    raise NotImplementedError
>  end
>  
>
That's correct behaviour. The DBDs have to overwrite this method. If 
not, than it's an error.

>Perhaps it would be safer to make this a null operation?
>
Have you ever seen the NotImplementedError exception? Probalby not :-)
Look at DBI::StatementHandle, which is what you call.

>I'm also not sure about 'select_all'. It calls 'fetch_all', but is that
>guaranteed to free up the cursor after it has finished?
>
This one should be safe (at least if there's no bug in it :).

Whenever you get back a StatementHandle (sth), you have to call #finish on it yourself
(except with block form). If you don't get one, you can't call it, so it's called for you.

So, whenever possible use the block forms. They are much safer, as they clean up everything for you.

BTW, this all is written down in Ruby Developers Guide. I've heard on ruby-talk, that's currently very
cheap if you live in US.


Regards,

  Michael