Space reuse in H2 embbeded DB

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. try to insert 2000 records in a table, the DB size would be around 1 MB
2. Delete 500 records , the size is still 1 MB ( we should run compaction!)
3. Insert another 2000 record, the space grows to 2 MB, The empty space 
produced by deleting 500 is not reused??

What is the expected output? What do you see instead?

Based on the H2.pdf documentation, the empty space should be reused i.e 
the size should be less than 2 MB for 2nd insertion.

What version of the product are you using? On what operating system, file 
system, and virtual machine?

i'm using H2 version 1.1.107 , On Windows XP , with Java 1.5

Do you know a workaround?

if we empty the table everytime prior to insertion the space is reused.
COULD YOU PLEASE ADVICE US WHICH VERSION OF H2 HAS FIX TO THIS PROBLEM

How important/urgent is the problem for you?

We are doing a new component for Bank of New york

In your view, is this a defect or a feature request?

I think it is a Defect  

Please provide any additional information below.

Original issue reported on code.google.com by Hiher...@gmail.com on 10 Feb 2009 at 9:14

[GoogleCodeExporter]

insert 2000 => 1 MB
delete 500 => 1 MB
insert 2000 > 2 MB (yes, but according to my calculation you have now 3500 
records)

From the described procedure I believe it is not a bug. If you think it is, 
please
reopen the bug and add a test case, for example a SQL script as in:

drop table test;
create table test(id int primary key, name varchar);
insert into test select x, 'Hello World' from system_range(1, 2000);
-- now ... MB
delete from test where id < 500;
insert into test select x+2000, 'Hello World' from system_range(1, 2000);
-- now ... MB

In any case, the database files don't shrink in the current release. I am 
working on
a solution, but that will take several month until it is finished and tested (a 
new
storage mechanism). To compact a database off-line, see
http://www.h2database.com/html/features.html#compacting (I know this is not a
solution for your however).

Original comment by thomas.t...@gmail.com on 11 Feb 2009 at 7:06

• Changed state: Invalid