New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
GTTs do not release used space [CORE6043] #6293
Comments
Modified by: Miroslav Djorov (mdjorov)description: Global temporary tables created with ON COMMIT DELETE ROWS don't release used space in the temp file when COMMIT RETAINING or ROLLBACK RETAINING is used. => Global temporary tables created with ON COMMIT DELETE ROWS don't release used space in the temp file when COMMIT RETAINING or ROLLBACK RETAINING is used. |
Commented by: Miroslav Djorov (mdjorov) This is a test script using COMMIT RETAIN. Using ROLLBACK gives the same results |
Modified by: Miroslav Djorov (mdjorov)Attachment: GTT_TEST.sql [ 13337 ] |
Modified by: Miroslav Djorov (mdjorov)description: Global temporary tables created with ON COMMIT DELETE ROWS don't release used space in the temp file when COMMIT RETAINING or ROLLBACK RETAINING is used. => Global temporary tables created with ON COMMIT DELETE ROWS don't release used space in the temp file when COMMIT RETAINING or ROLLBACK RETAINING is used. |
Modified by: @hvladassignee: Vlad Khorsun [ hvlad ] |
Commented by: @reevespaul To be honest, I would expect that behaviour from COMMIT/ROLLBACK RETAINING because that is how RETAINING works. |
Commented by: Miroslav Djorov (mdjorov) But all records are gone after RETAINING. They aren't visible any more in the transaction that inserted them. If the transaction is closed after the RETAINING, the space is still unusable. I've tried it with 2 connections to the database. One that do all the work with the GTT, and the other one just to have one so the file won't be deleted. Even after I use INSERT, COMMIT RETAINING, SELECT, COMMIT, DISCONNECT, the space is not reused from new INSERTS from new connections. And in some moment, the disk space is over and server crashes. |
Commented by: @hvlad The data of GTT ON COMMIT DELETE ROWS table should be visible after commit retaining. The correct fix it to make records visible but it could break many existing applications that rely on Therefore i offer following solution: - in v2.5 fix the disk space leakage and leave records not visible after commit retaining (i.e. clear GTT data) - in v3 add per-database setting that will rule engine behavior: - in v4 add same setting but make correct behavior (don't clear GTT data on commit retain) by default. |
Commented by: @asfernandes Since the problem is a *clear* bug, people should not depend on it, therefor I may agree to the plan for v2.5 and v3, but for v4 I think there should not be a config allowing the bug behavior. |
Commented by: Sean Leyne (seanleyne) I agree with Adriano, it is a bug that needs to be squashed. |
Commented by: @dyemanov "Commit" inside "GTTClearOnCommitRetain" looks confusing, as AFAIU the issue affects ROLLBACK RETAINING as well. Maybe something like ClearGTTAtRetaining? |
Commented by: @hvlad Rollback should clear GTT in any case, isn't is ? :) |
Commented by: @dyemanov Damn, surely it should ;-) |
Commented by: @hvlad After more thinking on issue I'm not so sure that rollback retaining should clear GTT. create global temporary table gtt (id int) insert into gtt values (1); -- point 1 insert into gtt values (2); -- point 2 What results is expected at points (1) and (2) ? The possible answers are: b) gtt data is preserved on both commit and rollback retaining c) gtt data is preserved on commit retaining and cleared on rollback retaining PS Unfortunately, there is no exact definition of "retaining" in IB docs, so we must decide ourself |
Commented by: @hvlad The patch against disk space leakage is committed into v2.5, |
Commented by: @asfernandes I don't understand the (B) option. Please explain: - What value it will see (1 or 2)? |
Commented by: @hvlad > - What value it will see (1 or 2)? 1 of course, second insert must be undone by rollback - there is no question > - What you mean by "gtt data is preserved" in "rollback retaining"? technically, rollback retaining could: |
Commented by: @asfernandes > - undo actions made since last retaining point only (preseve data existed before last retaining point) So I agree with B option. |
Commented by: @dyemanov So do I. |
Modified by: @hvladstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.5.9 [ 10862 ] Fix Version: 3.0.5 [ 10885 ] Fix Version: 4.0 Beta 2 [ 10888 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Miroslav Djorov (mdjorov)
Attachments:
GTT_TEST.sql
Global temporary tables created with ON COMMIT DELETE ROWS don't release used space in the temp file when COMMIT RETAINING or ROLLBACK RETAINING is used.
When inserting records in a GTT and the transaction is finished with COMMIT or ROLLBACK the space in the file is reused, but if a transaction insert records and then we call COMMIT or ROLLBACK RETAINING the temporary file fb_table_XXX keeps growing in size. In situations where there are 24/7 connections to the database the temp files are not deleted and with this constant growing they eat the disk space.
All records in the table are not visible after the COMMIT/ROLLBACK action as it supposed to be, but the space is not released.
Commits: d3743e0 a0470f9 3f5e93a 7ce2a34 cd7f111 d1bff87 a1cfe74 7a7f3b6
The text was updated successfully, but these errors were encountered: