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

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.

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?

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

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?

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.

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.