Forum OpenACS Q&A: Ran out of database pools (pool2 pool3 pool1)

Request notifications

What does this error message refer to?  I'm just using the standard DB API commands: db_0or1row, db_foreach, db_dml, etc.

Thanks, Kevin Murphy

It was thrown inside db_dml doing an insert in one of my pages.

The postgres (7.2.3) log doesn't reveal anything.

The code (in openacs 4.6 / aolserver 3.4.2) looks like this:

if [catch {db_dml queue_it {
insert into adv_target_queue  (user_id,target_id,user_letter_id,queued)
values (:user_id, :target_id, :user_letter_id, CURRENT_TIMESTAMP)}} catch_err] {
    ns_log Notice "Error: $catch_err for (user/target/letter) ($user_id/$target_id/$user_letter_id)"
}

It is nested inside three db_foreach's.

Collapse
Posted by Dave Bauer on
You answered your own question. db_foreach three levels deep and another query inside that.

AFAIK you can't have queries like that 4 levels deep. You might consider rewriting part of the loop in pl/sql.

Collapse
Posted by Don Baccus on
Dave's half-right - you've nested four deep and ran out of pools.

Where he's wrong is that all you need to do is to add another pool to your configuration.

The bad news is that nesting db_foreach three deep is a very expensive way to calculate dynamic data.  If each db_foreach process 10 rows, the inner query will be executed 100 times.  If each process 50 rows ... oh boy.

Are you sure you can't combine the nested loops?

Collapse
Posted by Kevin Murphy on
Dave,

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,
Kevin

Collapse
Posted by Kevin Murphy on
Don,

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.

-Kevin Murphy

Collapse
Posted by Don Baccus on
If it's a one-timer, just add more pools :)  Responsiveness is really just important when you expose pages to the public ...
Collapse
Posted by Don Baccus on
It needs a separate database handle for each loop because once you execute the inner loop, it needs to retrieve the next row from the query result formed by the outer loop (db_foreach).  If the inner db_foreach overwrote the outer db_foreach's data people would pout and complain :)
Collapse
Posted by Eduardo Pérez on
We just hit the same problem when doing the IMS QTI exporter of assessment and adding another pool solved our problem.
Is adding another pool the recommended way?
Should OpenACS include another pool by default?
(If not assessment won't work in a default install)
Is there another solution?
Collapse
Posted by Don Baccus on
If at all possible please rewrite your queries to not use a fourth pool. It is much better to do a more complex query joining multiple tables if possible. Note that in pure SQL, outside an application framework, you can't do any nesting of this sort anyway ... nested query loops can in general be rewritten into a single SQL query with post-processing of the rowset in the db_foreach loop.

We don't really want to force everyone to define four pools be default. Nor do we want the installation of a particular package force people to rewrite their init .tcl file.

The main reason, though, as mentioned above in 2003 ... DEEPLY NESTED DB_FOREACH LOOPS WILL NOT SCALE. Joining multiple tables using a column index DOES SCALE.

Can you describe what you're trying to do in this deeply nested query structure in more detail?

Collapse
Posted by Andrew Piskorski on
Keep in mind that each database handle is using its own entirely independent transaction. If you use db_transaction, it will not work across multiple database handles from multiple pools (or even from same pool, I think), but it won't throw a warning or error either. It will just not enforce a transaction, because it can't.

I have personally never seen even one case where using two database handles and pools at once via db_foreach was actually a good idea. Don, do such cases exist at all in real life? Maybe OpenACS should ship a default AOLserver config file that allows use of only one database pool at a time?

After all, people can always configure a 2nd, 3rd, 4th, or Nth pool if they really want to, and know what they're doing. The automatic transparent grabbing of additional independent database handles via db_foreach (introduced in or shortly before ACS 4.0) always seemed like a dangerous feature to me, most especially because of the way it breaks transactional semantics, which no one new to OpenACS is going to realize. I suspect newbies would be better off just getting an error message...

Hm, I wonder if any other database drivers (e.g., Oratcl, SQLite) let you issue multiple concurrent SQL statements which are all part of the same transaction, or if that's even possible with the Oracle OCI or PostgreSQL C libraries. Anybody know?

Collapse
Posted by Don Baccus on
You are right about transaction semantics, but it doesn't matter typically if you're doing nested SELECTs to display data in a table, which is why people typically nest db_foreach.

This doesn't making nesting them a good idea, but it doesn't make nesting them particularly dangerous either. Just not the right way to write queries to display data.

All db_* API procs grab handles automatically, and there are many cases where it makes perfectly good sense to do so. Shipping OpenACS defaulted to one pool would break a large amount of perfectly well-behaved code.

Collapse
Posted by Eduardo Pérez on
The code is used to export an assessment to IMS QTI v1 file:
http://cvs.openacs.org/cvs/*checkout*/openacs-4/packages/assessment/www/asm-admin/export/index.vuh
we need to do nested queries to generate the whole XML IMS QTI v1 file structure.
Is there an easier way?
We are not using a fourth pool now but I don't know how to change the code if there was only two pools (or if we needed that fourth pool in the future)
Maybe, should we be fetching all the data into memory first?
Is that the recommended design?
Collapse
Posted by Dave Bauer on
Basically you need to use a regular foreach instead of db_foreach.

In your case I suggest selecting the outer dataset in a multirow with db_multirow, then using template::multirow::foreach on that multirow data.

If you have a list of ids or another single column to loop over in the outer loop, you can just use db_list instead of db_multirow for the outer query.

Hm. I forgot about the (probably more typical?) cases where a proc several call layers deep might grab a 2nd handle to do some totally unrelated query, as opposed to the db_foreach case. (Better than the old days 3.x days of having to always refactor code to pass in the db handle.) That's probably why only enabling one db pool would break lots of stuff?
Collapse
Posted by Don Baccus on
Yes.