Skip to content
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

Closed
firebird-automations opened this issue Apr 5, 2019 · 22 comments
Closed

GTTs do not release used space [CORE6043] #6293

firebird-automations opened this issue Apr 5, 2019 · 22 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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.
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 R

=>

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Miroslav Djorov (mdjorov)

This is a test script using COMMIT RETAIN. Using ROLLBACK gives the same results

@firebird-automations
Copy link
Collaborator Author

Modified by: Miroslav Djorov (mdjorov)

Attachment: GTT_TEST.sql [ 13337 ]

@firebird-automations
Copy link
Collaborator Author

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.
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.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @reevespaul

To be honest, I would expect that behaviour from COMMIT/ROLLBACK RETAINING because that is how RETAINING works.
Whether that should apply to GTT's is another matter.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

The data of GTT ON COMMIT DELETE ROWS table should be visible after commit retaining.
The actual bug is that engine keeps this data but does not make it visible after commit retaining.

The correct fix it to make records visible but it could break many existing applications that rely on
buggy behavior (GTT is empty after commit retaining).

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:
GTTClearOnCommitRetain = 1 will work as v2.5 above,
GTTClearOnCommitRetain = 0 will not clear GTT data on commit retaining and will make records visible
after commit retaining.
By default GTTClearOnCommitRetain will be set to 1 to preserve backward compatibility with 2.5

- in v4 add same setting but make correct behavior (don't clear GTT data on commit retain) by default.
Also, if time permits, we will implement TRUNCATE TABLE statement (probably for GTT only for the start)

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

I agree with Adriano, it is a bug that needs to be squashed.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

"Commit" inside "GTTClearOnCommitRetain" looks confusing, as AFAIU the issue affects ROLLBACK RETAINING as well. Maybe something like ClearGTTAtRetaining?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Rollback should clear GTT in any case, isn't is ? :)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Damn, surely it should ;-)

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

After more thinking on issue I'm not so sure that rollback retaining should clear GTT.
If we agree that GTT's data is part of transaction context and both commit retaining and rollback retaining should
preserve transaction context than we could accept that rollback retaining should undo actions made since
previous commit\rollback retaining only. For example:

create global temporary table gtt (id int)
on commit delete rows;

insert into gtt values (1);
commit retain;

-- point 1
select * from gtt;

insert into gtt values (2);
rollback retain;

-- point 2
select * from gtt;

What results is expected at points (1) and (2) ?

The possible answers are:
a) current (buggy?) behavior
no rows at both points

b) gtt data is preserved on both commit and rollback retaining
one row at point (1), one row at point (2)

c) gtt data is preserved on commit retaining and cleared on rollback retaining
one row at point (1), no rows at point (2)

PS Unfortunately, there is no exact definition of "retaining" in IB docs, so we must decide ourself

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

The patch against disk space leakage is committed into v2.5,
please try next snapshot build.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I don't understand the (B) option. Please explain:

- What value it will see (1 or 2)?
- What you mean by "gtt data is preserved" in "rollback retaining"?

@firebird-automations
Copy link
Collaborator Author

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:
- release all pages of GTT (clear GTT data), or
- undo actions made since last retaining point only (preseve data existed before last retaining point)

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> - undo actions made since last retaining point only (preseve data existed before last retaining point)

So I agree with B option.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

So do I.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: 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 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment