On Wed, Dec 14, 2011 at 3:04 AM, p51b.mustang <p51b.mustang@...> wrote:

>
> On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems.
> On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results.

Trust the right side, Luke. Maybe someone has fixed this in a more
recent version of Firebird, but it's a
characteristic bug of databases that Jim Starkey writes. Unless
there's a sort (e.g. order by, distinct,
grouped) they get records one a time rather than finding all
qualifying rows first as the standard anticipates.
Rdb/Eln, InterBase, Firebird, Netfrastructure, Falcon, and, until
recently NuoDB all go into an infinite loop
if you have any rows in table A an do something like this:

insert into A select * from A

Retrieving all the rows and data first is really inefficient.
Retrieving the rows and retaining only the db-keys
(or equivalent) does the same amount of I/O as retrieving data first,
but doesn't use as much memory or
temporary disk space. The solution that NuoDB implemented is to keep
a sparse bitmap of the db-keys of
the inserted records and not reinsert them when they come back from
the select. Note that db-keys are not
necessarily monotonically increasing, so the query has to run until it
exhausts the result set, not just stop
when it finds the first "new" record. And, of course, you can't just
ignore records created by your transaction,
or running the query twice wouldn't get the right answer.

It's possible to do the same thing using the information in the
savepoint undo list, I think, and maybe
Firebird implemented that after 2.0.

Good luck,

Ann