Forum OpenACS Q&A: Re: Ran out of database pools (pool2 pool3 pool1)
AFAIK you can't have queries like that 4 levels deep. You might consider rewriting part of the loop in pl/sql.
Aha. I added another pool in the aolserver config tcl file, and the problem went away. Is this OK to do?
I mentioned the nesting because I thought it might have something to do with it, even though I don't understand how the DB handles work. Naively, I ask: why can't nested queries just use different handles in the same pool?
Thanks; our posts crossed in the ether. Luckily, this is a one-time only data processing script, and the performance doesn't matter to me. You are right that various separate queries could probably become joins, if that's what you're saying.
Also, it is not strictly necessary to hold a database handle open for smaller result sets; I could just grab the whole query result into a data structure and loop over that.