David Johnson wrote:

>
> DB2 handles this by performing the select first, then inserting after
> all records have been selected.
>
> This has the downside that it can be very memory intensive.

No kidding. That's why I was looking for a solution that provided the
same results without needing to cache a potentially huge record set.
Those familiar with the internals realize that Firebird caches result
sets only when it performs a sort.

> However, it
> is guaranteed not to loop since the selection is finished before any
> inserts occur,

Err, I guess huge record sets are by definition smaller than infinite
record sets, but it would be good to find a way to avoid the whole
situation.

> If Firebird is capable of supporting nested transactions (really big
> if), then the insert could be separated into a nested transaction that
> is owned by the user's transaction. In this scenario, the select runs
> under the user's transaction. The select cannot see anything that is
> inserted by the nested transaction, so there is no loop.

That does get into some messy stuff around a commit. A commit must be a
single atomic operation - like changing two bits on a single page.
Having to commit two different transactions (self and child) introduces
the possibility of a failure between the two writes, leaving a partial
commit. Since our current nested transactions are based on an undo log,
I still think that's a better place to look.


Regards,


Ann