Subject Re: [Firebird-Architect] the insert into t select * from t infinite loop
Author Jim Starkey
Adriano dos Santos Fernandes wrote:

>I more or less know how it works now. Please say if there is some error.
>insert into t (x) select x from t
>is translated to
>for select x from t into :x do
> insert into t (x) values (:x);
>INSERT: execute before insert triggers, write the record to one
>datapage, update indexes, execute after insert triggers.
>write the record to one datapage: one datapage is found through
>RDB$PAGES, if one is not found a new one is created and inserted in
>With my approach:
>The INSERTs (only from insert ... select) don't use pages that is
>already in RDB$PAGES, and the new pages are not inserted in RDB$PAGES.
>Some thing need to be extended to the insert in the for loop use pages
>created by himself and the allocated page numbers should be stored.
>After all records are writed: insert page in RDB$PAGES, update indexes,
>execute after insert triggers, insert another page in RDB$PAGES, ...
>I see only one design problem: how after insert triggers can work with
>pages that have more than one record?
>The rest is all implementable.
Adriano, this has virtually nothing to do with how the engine actually
works. Data page numbers, for example, aren't stored in RDB$PAGES,
they're stored in pointer pages. The record number represented in the
indexes is a composite of relative pointer page number, slot on pointer
page, and slot on data page. Since in your scheme you couldn't know
either the relative pointer page or slot, the record number couldn't be
determined. Another problem is that triggers and constraints have to be
able to see the new record, so hiding it elsewhere doesn't work at all.

The problem is, in fact, quite simple, and Ann has sketched the
solution. All that is necessary is to track the record numbers of newly
inserted records so the outer select can ignore them. This is
particularly easy since a bitmap of those records is already maintained
for verb backout. All we need to do is use it. And the best way to use
it is just another very simple RSB type.