Forum OpenACS Q&A: Response to PostgreSQL driver usage
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.