Subject Re: the insert into t select * from t infinite loop
Author David Johnson
Both of the offerings I can bring to the table are dependent on the
capacity to split the SQL into two distinct parts. Both are based in
relative ignorance of Firebird's internals - any feedback would be

DB2 handles this by performing the select first, then inserting after
all records have been selected.

Conceptually, behind the scenes, it breaks the statement down into two

1. open cursor for "select * from a where a.t_val in (select t_val from

2. insert into t from cursor

This has the downside that it can be very memory intensive. However, it
is guaranteed not to loop since the selection is finished before any
inserts occur, and it is not likely to thrash the disk I/O too badly.

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.