Forum OpenACS Development: ns_db gethandle gives error 'could not allocate 1 handle from pool "pool1"' Naviserver on Windows

1: ns_db gethandle gives error 'could not allocate 1 handle from pool "pool1"' Naviserver on Windows

Posted by Brian Fenton on 03/08/17 06:07 PM

This is a strange one. It's happening on Naviserver on Windows (both Postgres and Oracle databases) with a clean install of OpenACS 590. It would be good to confirm if it is a bug, or that I haven't set up correctly or understood something in how Naviserver does the the database pools.

If I enter the following in the Developer Shell:
set db [ns_db gethandle]

I get this error:
could not allocate 1 handle from pool "pool1"

Trying to specifically choose pool1 gives the same error:
set db [ns_db gethandle "pool1"]

However, if I choose pool2 or pool3, it works!
set db [ns_db gethandle "pool2"]
Returns "nsdb1".

When I try a query with a command like db_string it works fine, e.g.
set object_count [db_string get_count "select count(*) from acs_objects"]

So it doesn't seem to be a problem of not being able to allocate handles.

Digging deeper, the following also works (taken from db_string source):
db_with_handle -dbn "" db {
set selection [db_exec 0or1row $db full_name "select count(*) from acs_objects"]
}
set result [ns_set value $selection 0]

Next I attempted to dig into db_with_handle, and this is where it starts to get hairy. The if clause that checks if $db_state(n_handles_used) >= [llength $db_state(handles)] always seems to fail, as $db_state(n_handles_used) has a value of 0, and $db_state(handles) has a value of "nsdb0". If that is the case the it's not fully clear to me where the db_state array is getting populated. The only other place I see a call to ns_db gethandle is in db_bootstrap_checks and db_bootstrap_set_db_type, which get sourced at startup, and don't seem to be reporting any issues in the error log.

For anyone still reading, could I ask a favour? Could you check if "ns_db gethandle" works on your installation in the developer shell, especially those of you on Unix/Linux? It would be good to see if this is a Windows-only thing.

Any other insights would be appreciated.
Brian

2: Re: ns_db gethandle gives error 'could not allocate 1 handle from pool "pool1"' Naviserver on Windows (response to 1)

Posted by Gustaf Neumann on 03/08/17 07:21 PM

just a short reply (i am very short on time). An OpenACS application developer should never do a [ns_db gethandle] manually, since this bypasses the handle-managemement form OpenACS, using multiple pools. A application developer should use instead the db* interface, or maybe "db_with_handle".

From every pool, every request gets just one handle (and might put it back manually or at the end of the request). The handle information is managed/reused in OpenACS via global tcl variables such that a complex page with e.g. 100 sql requests needs just a single "gethandle" request.

The question whether or not a manual "ns_db gethandle" returns a value or not boils down to the question whether or not the request has already requested a handle. On OpenACS.org, the command

 set db [ns_db gethandle]

succeeds, but

 set db1 [ns_db gethandle]
 set db2 [ns_db gethandle]

fails. It seems, that in your instance, probably the templates issue already requests from pool1, therefore the request from pool1 fails but the request for pool2 succeeds. This is the same behavior as on Linux or Mac OS.

Hope this helps.

3: Re: ns_db gethandle gives error 'could not allocate 1 handle from pool "pool1"' Naviserver on Windows (response to 2)

Posted by Brian Fenton on 03/08/17 08:13 PM

Hi Gustaf

thanks very much for replying when you are under so much pressure - I do appreciate it.

I understand the situation much more now, and yes it makes sense that the templating system is getting the first handle. In earlier versions of OpenACS under AOLserver, it didn't seem to cause a problem. Unfortunately we have some legacy code that does call ns_db gethandle, so I'll investigate what's involved in re-writing that using db_with_handle or similar.

all the best
Brian

4: Re: ns_db gethandle gives error 'could not allocate 1 handle from pool "pool1"' Naviserver on Windows (response to 3)

Posted by Gustaf Neumann on 03/08/17 10:07 PM

I guess, it is not the templating system that get's the handle, but the master template might call some SQL function, that causes the handle allocation. concerning "ns_db gethandle": I do not remember any changes in OpenACS (or AOLserver/NaviServer) in these regards since i started to use OpenACS, which was more than 10 years ago... but i know there is much code out there, which predates this and often runs quite well.