FirebirdSQL
sequential insert/delete in a single transaction [CORE3294]
Submitted by: Aleksey Sudakov (fullzero)
CREATE GLOBAL TEMPORARY TABLE TABLE1 ( FIELD1 INTEGER NOT NULL ) ON COMMIT DELETE ROWS;
set term ^;
create procedure SP1 as begin insert into Table1 values (1); delete from Table1; end^
set term ;^
If the procedure is called several times consecutively in a single transaction, is constantly growing fetches quantity:
1. Fetches from cache = 24 2. Fetches from cache = 17 3. Fetches from cache = 19 4. Fetches from cache = 21 5. Fetches from cache = 23 6. Fetches from cache = 25 7. Fetches from cache = 27 8. Fetches from cache = 29 9. Fetches from cache = 31
Commented by: Sean Leyne (seanleyne)
This is not a support forum, please re-post this to the Support mailing list @ http://YahooGroups.com
The number of fetches increases due to the Multi-Generational Architecture (MGA) design of the Firebird engine. Each row in a table must be checked to see which version of a row is visible/appropriate for the current transaction.
Modified by: Sean Leyne (seanleyne)
status: Open [ 1 ] => Resolved [ 5 ]
resolution: Won't Fix [ 2 ]
Commented by: Aleksey Sudakov (fullzero)
rows created and deleted in the same transaction cannot be visible to other transactions under any circumstances and thus can be considered as garbage and erased immediatelly (c) Dimitry Sibiryakov
Commented by: @hvlad
Sean,
this ticket was created because i asked to create it. Scenario, when record is inserted and deleted in the same transaction, handled in not most optimal case. It could completely remove record versions after delete but it is not done currently. Therefore it creates more and more not needed records. So, this is valid request for improvement. Probably it is not high importance, but i see no reason to reject it.
Modified by: Sean Leyne (seanleyne)
status: Resolved [ 5 ] => Reopened [ 4 ]
resolution: Won't Fix [ 2 ] =>