LOG=2 option appears to be broken. Index file size grows out of control

[Alex]

Hello Thomas,

Below is the test program that demonstrates the issue. Basically when
the database is open with the option LOG=2 and the same rows are
updated many times the size of the index file grows rapidly. The
performance of the system slows down with the file size growth as
well. The most interesting item is that the columns updated are not
participating in the index, so there is no reason to update the index
at all.

Could you take a look please ?
Alex
===================================================================================

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/*
*
* In this test there are two sets of url options when creating the
initial
* connection to the database. One uses log=2 and the other does
not. When
* the connection containing log=2 is used the index file grows very
large
* in relation to the data file. Between executions of this
application please
* delete the database that is created.
*
* Something interesting is that the column being indexed is not even
being
* updated in the update query so I would think that the index file
should not
* even be altered?
*
*/
public class DbIndexTest
{
public static void main(String[] args) throws Exception
{
File dbFile = new File(".", "testdb");

/*
* Use the first urlOptions to see a normal execution of this
test.
* Use the second urlOptions to see the index grow extremely
large
* compared to the data file size.
*/
String urlLatestOptions1 =
"MAX_LOG_SIZE=1;DB_CLOSE_DELAY=-1;TRACE_MAX_FILE_SIZE=1";
String urlLatestOptions2 =

"LOG=2;MAX_LOG_SIZE=1;DB_CLOSE_DELAY=-1;TRACE_MAX_FILE_SIZE=1";

String urlLatest = "jdbc:h2:" + dbFile + ";" +
urlLatestOptions1;

String username = "sa";
String password = "";

Class.forName("org.h2.Driver");

Connection conn = DriverManager.getConnection(urlLatest,
username, password);

runTests(conn);

conn.close();
}

public static void runTests(Connection conn) throws Exception
{
Statement stmt = conn.createStatement();

try
{
stmt.execute("create table fs_dirs(" +
"type tinyint not null," +
"dir_time bigint not null," +
"location_id tinyint not null," +
"partition tinyint not null)");

stmt.execute("create index fs_dirs_time_idx on " +
"fs_dirs(dir_time)");

// add the initial row
stmt.execute("insert into fs_dirs" +
" ( type, dir_time, location_id, partition)" +
" values (1, 123456789, 2, 7)");

// update the row previously created many times
for(int i = 0; i < 100000; i++) {
stmt.executeUpdate("update fs_dirs" +
" set location_id = 6, partition = " +
Integer.toString(i % 10) +
" where type = 1 and dir_time = 123456789");
}
}
finally
{
stmt.close();
}
}
}

[Thomas Mueller]

Hi,

Thanks a lot for the test case! I now found out what the problem is:
the test case only has one row, and updates it over and over again.
The update is converted to delete+insert. There is an 'optimization'
in BtreeIndex.remove: when the last row is deleted, the index is
truncated. Unfortunately, the space is not re-used in this case. This
problem will be fixed in the next release.

Regards,
Thomas