Forum OpenACS Q&A: PostgreSQL driver usage

Posted by Peter Holt Hoffman on

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 gethandles having 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 time.

Prior to discovering releasehandle I got this error as soon as the second subroutine tried to gethandle which made sense to me once I realised what was going on there.

Has anyone else seen this sort of thing? TIA!

Posted by Ben Adida on
The error isn't in the driver, but the design of your code. However, you have nothing to worry about, everyone makes this mistake at least once, and it's kind of hard to understand exactly what the problem is.

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.

Posted by Peter Holt Hoffman on

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.

Posted by Don Baccus on
It does indeed free the handle to the pool.  As Ben points out, though, you're not running out of handles, you're running into the AOLserver restriction that rejects a gethandle on a pool when you already own one.  He's done a great job of explaining why the restriction exists, so I won't add to it.

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.