Hi,
I encountered that the DB file size is growing when using a table created
with TEMPORARY and NOT PERSISTENT.
Reading the documentation I expect that this table type is stored in memory
only so I wonder if it is ok that the DB file size grows.
Attached is a small test program that creates a database with one table:
CREATE LOCAL TEMPORARY TABLE MEMVALUES (value VARCHAR NOT NULL PRIMARY KEY)
NOT PERSISTENT
The program adds random values to this table but ensures that this value is
not added twice:
INSERT INTO MEMVALUES (value)
SELECT '<random>' FROM DUAL
MINUS
SELECT value FROM MEMVALUES
<random> is a random number.
After inserting 10002 rows the database file starts to grow. The program
exits here but without exiting the db file grows continously.
Is this behaviour ok? I expect that temporary not persistent tables must
not have an influence on the db file size.
Uli
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
import java.sql.*;
import java.util.concurrent.ThreadLocalRandom;
public class H2MemTablesTest
{
private static long getDbSize(Statement stmt)
throws SQLException
{
ResultSet rs = stmt
.executeQuery("SELECT value FROM information_schema.settings where NAME = 'info.PAGE_COUNT'");
rs.next();
long pagecount = rs.getLong("value");
rs.close();
rs = stmt.executeQuery("SELECT value FROM information_schema.settings where NAME = 'info.PAGE_SIZE'");
rs.next();
long pagesize = rs.getLong("value");
rs.close();
return pagecount * pagesize;
}
public static void main(String[] args)
throws ClassNotFoundException, SQLException
{
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/h2/test", "h2", "test");
Statement stmt = conn.createStatement();
stmt.execute("CREATE LOCAL TEMPORARY TABLE MEMVALUES (value VARCHAR NOT NULL PRIMARY KEY) NOT PERSISTENT");
long dbSize = getDbSize(stmt);
for (int i = 0; i < 100000; i++)
{
// insert all new values - this should not have an effect on db size because memvalues
// is a table kept in memory
stmt.execute("INSERT INTO MEMVALUES (value) SELECT '" + ThreadLocalRandom.current().nextInt()
+ "' FROM DUAL MINUS SELECT value FROM MEMVALUES");
long dbSizeNow = getDbSize(stmt);
// compare the DB size
long dbDiff = dbSizeNow - dbSize;
if (dbDiff > 0)
{
// DB size is larger than it was after opening the DB
// This should not happen!?
System.err.println("Unexpected DB size growth (" + dbDiff + " bytes) after inserting " + i + " rows");
// now check if the DB shrinks when compacting
stmt.execute("SHUTDOWN COMPACT");
conn = DriverManager.getConnection("jdbc:h2:~/h2/test", "h2", "test");
stmt = conn.createStatement();
long dbSizeAfterCompact = getDbSize(stmt);
System.err.println("DB size after compact: " + dbSizeAfterCompact + " Growth: "
+ (dbSizeAfterCompact - dbSize));
return;
}
}
stmt.close();
conn.close();
}
}
