Thanks Ann, Being of two minds I am pleased that I decided to check with this group. As I am migrating from 1.5 to 2.5 I can not consider savepoint as two production DBs are still 1.5. The process I am trying to achieve is at least annual, and at most very infrequently, that efficiency will be secondary to readability and understandability for my successors. I shall also include a precise of these responses into a comment field; just in case. Thanks again David
--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote: > > 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 >
