Forum OpenACS Q&A: PostgreSQL driver usage
Hello, I hope I am in the right place for this. I am using the PostgreSQL driver (version 2.3) with PostgreSQL 6.5.2 on RH6.1 Linux and I am having a problem.
My ADP code is written as subroutines which do their own
gethandle as needed (no subroutine depends on something
else having set up an environment). As soon as possible I do a
releasehandle. I am getting this fatal error in the
AOLserver logs: "...nsd.db: thread already owns 1 handle..." when I
try to do some of the
gethandle calls. I am not getting
an error in the PostgreSQL logs, even with a '-d 9' setting. It
seems to happen when I exceed some unknown limit (the same sort of
code runs in several subroutines). In my db/pool section I do have
Connections set to 4 although I don't knowingly use more than 1. The
error does not correlate with 4
been done. Also, this is happening even when only a single user is
accessing the system so it does not seem to be load related. I have
checked and every
gethandle does have a
releasehandle so I think I only have 1 handle at a
Prior to discovering
releasehandle I got this error
as soon as the second subroutine tried to
which made sense to me once I realised what was going on there.
Has anyone else seen this sort of thing? TIA!
Remember that AOLserver maintains a pool of connections to the database. The serving of one ADP page is handled by one thread. Now, if there is just one pool of db connections and you attempt to grab more than one connection from this pool in the same page, you risk deadlock (think about the simplified case of having two threads, two db conns in the pool, and each thread grabbing one db conn and then waiting forever to get the second db conn...). Thus, you cannot request a db conn from a given pool if the thread in question already owns a connection from that pool.
This is why AOLserver allows you to maintain several pools, and why in the ACS we use the "main" db pool for in-page db conns, and we use the "subquery" db pool for procedures that need to allocate their own db conn.
The solution has to do with how you structure your code: if you have procs calling other procs, it's a good idea to pass around a db conn through the procedure calls. Recently, I've taken to creating procs that take a db conn as an optional last argument. If the db conn is passed in, the proc uses it, otherwise it grabs a new db conn from the subquery pool and returns it at the end.
You can find the procs check_db_var and cleanup_db_var in acs3-pg/tcl/teams.tcl that will make this easier.
Thank you for your quick response. I suspected that something like this was the source of my problem. What is still confusing me though is what is the purpose then of
releasehandle (for which the AOLserver documentation says "puts the handle back in the pool") if it doesn't release the handle back to the pool? For a while that had fixed my problem which added to the confusion. I ask only in an attempt to complete my understanding of how this all works.
That's the meaning of the error message.
If you need more than one handle from a pool, use a single gethandle to allocate all of them at once.