There are several aspects:
a) transaction semantics
b) handle management
c) performance aspects
For (a): when there is a transaction with some outer query (using e.g. "db_exec select" and an inner query (also a transaction), how to control, the scope of the rollback?
b) If there are nested db_foreach, implemented via "db_exec select", then on each nesting level a new handle is fetched from a new pool. So there is an obvious problem with nestings (do API calls use this or not?) and with the number of handles needed per pool (how much handles should be allocated per pool?).
c) If one is using SQL calls inside a db_foreach, and the db_foreach returns e.g. 1000 rows, then the inner part of the loop executes 1000 SQL queries. Executing a high number of even quick SQL queries is often a performance issue. It is nearly always better to perform a more complex SQL query at the first place.
Part of the problem is the usage of "db_exec select" in the classical "db_foreach" implementation of OpenACS [1]. If one avoids this (by using e.g. e.g. db_list_of_litsts, or db_list_of_ns_sets [2]) in the implementation of db_foreach, then the same handle can be reused. Therefore, one is not running into the handle limitation, and the transaction semantics are clear.
As you can see [2], oacs-5-10 already avoids some of these issues. In application programs, looking at db_foreach constructs can help to get rid of performance issues. One can certainly continue to use db_foreach....
Hope, the helps more than it confuses.
all the best
-g
[1] https://github.com/openacs/openacs-core/blob/oacs-5-9/packages/acs-tcl/tcl/00-database-procs.tcl#L1403
[2] https://github.com/openacs/openacs-core/blob/oacs-5-10/packages/acs-tcl/tcl/01-database-procs.tcl#L1633
