H2 seems to create a huge number of orphan lobs since 1.4.183.

[EungJun Yi]

Hello.

I am using H2 database in embeded mode.

After upgrading to 1.4.184 from 1.3.176, INFORMATION_SCHEMA.lobs table grows rapidly. It takes only a week to grow from 140k records to 36million records.When I try to shutdown my app, it takes two hours to clean the table.

I have found the most of the records have "-3" in "table" field as follows:

ID     | BYTE_COUNT | TABLE
...
765194 | 12707      | -3
765195 | 1670       | -3
765196 | 1314       | -3
765197 | 1276       | -3
765198 | 382        | -3
765199 | 382        | -3
765200 | 434        | -3
765201 | 1299       | -3
765202 | 353        | -3
765203 | 277        | -3
...

As my reading of H2 source code, the record is for result set, created by LocalResult.cloneLobs method added at r5942.

It seems that H2 cleans the blobs only when the database is closed. You may think it is fine because the database seems to be closed automatically after closeDelay since there is no user session, but it is a problem for me because there are always some user sessions in my application.

I tested it with 1.4.182, 1.4.183, 1.4.184 and 1.4.186 and only 1.4.182 works fine.

Thanks for reading.

[Yi, EungJun]

On Fri, Mar 20, 2015 at 7:01 PM, EungJun Yi <semtl...@gmail.com> wrote:

Hello.https://code.google.com/p/h2database/source/detail?r=5936

I am using H2 database in embeded mode.

After upgrading to 1.4.184 from 1.3.176, INFORMATION_SCHEMA.lobs table grows rapidly. It takes only a week to grow from 140k records to 36million records.When I try to shutdown my app, it takes two hours to clean the table.

I have found the most of the records have "-3" in "table" field as follows:

ID     | BYTE_COUNT | TABLE
...
765194 | 12707      | -3
765195 | 1670       | -3
765196 | 1314       | -3
765197 | 1276       | -3
765198 | 382        | -3
765199 | 382        | -3
765200 | 434        | -3
765201 | 1299       | -3
765202 | 353        | -3
765203 | 277        | -3
...

As my reading of H2 source code, the record is for result set, created by LocalResult.cloneLobs method added at r5942.

Oops, the record is created ValueLobDb.copyToResult() method added at r5436.

It seems that H2 cleans the blobs only when the database is closed. You may think it is fine because the database seems to be closed automatically after closeDelay since there is no user session, but it is a problem for me because there are always some user sessions in my application.

I tested it with 1.4.182, 1.4.183, 1.4.184 and 1.4.186 and only 1.4.182 works fine.

Thanks for reading.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/WoFDlotV7fo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Fred&Dani&Pandora&Aquiles]

Hi,

I think the version 1.4.186 had some improvements in this area (http://www.h2database.com/html/changelog.html). I can't remember now if there's some protocol to follow when upgrading the version, but one alternative that you could try, is to test again with latest version.

Regards,

Fred

--
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...@googlegroups.com.

[Yi, EungJun]

Thanks for reply!

On Fri, Mar 20, 2015 at 9:20 PM, Fred&Dani&Pandora&Aquiles <zep...@gmail.com> wrote:

Hi,

I think the version 1.4.186 had some improvements in this area (http://www.h2database.com/html/changelog.html). I can't remember now if there's some protocol to follow when upgrading the version, but one alternative that you could try, is to test again with latest version.

I guess the improvement you are saying is:

> MVStore: orphaned lob objects were not correctly removed in some cases, making the database grow unnecessarily.

But I disabled MVSTORE and MVCC by "MVSTORE=FALSE;MVCC=FALSE". Does the improvement help me?

[Fred&Dani&Pandora&Aquiles]

Hi,

 

But I disabled MVSTORE and MVCC by "MVSTORE=FALSE;MVCC=FALSE". Does the improvement help me?

According to the changelog the improvement helps only with MV_STORE. In this case, I think Thomas or Noel are more qualified to answer this question.

Regards,

Fred 

[Thomas Mueller]

Hi,

> INFORMATION_SCHEMA.lobs table grows rapidly

Yes. This is a problem for both MVStore and PageStore. The problem is that CLOB and BLOB objects must still be available even after the transaction is complete, and I think even after the result set is closed. And even after the row is removed. In previous versions of H2, BLOB and CLOB objects were removed too early; now they are removed when the database is closed. This will fix the "LOB is removed" problem, but results in a growing LOB table, which contains references (table -3 is temporary lobs).

This is a problem, and I don't have a good solution for that I'm afraid. Closing the database or closing the connection from time to time are not good solutions I think. 

Maybe the temporary BLOB and CLOB objects should have a timeout (of let's say 1 minute by default)?

Regards,

Thomas

--

[Yi, EungJun]

> In previous versions of H2, BLOB and CLOB objects were removed too early

Does 1.3.xxx also have the problem? I am thinking to downgrade it if possible.

> Maybe the temporary BLOB and CLOB objects should have a timeout (of let's say 1 minute by default)?

It sounds nice. I need a workaround if there is no solution yet because the problem is quite critical for me. (I can't restart my application everyday)

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/WoFDlotV7fo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Noel Grandin]

On 2015-03-23 10:43 AM, Thomas Mueller wrote:
>
> This is a problem, and I don't have a good solution for that I'm afraid. Closing the database or closing the connection
> from time to time are not good solutions I think.
>

I can think of a few options

(1) add a manual command to clean them up

(2) change it so that the client can only read from a BLOB while a transaction is open. Flush any temporary BLOBs on
commit. This is how other databases work, but it would be a change for us. So maybe we would have to hide this option
behind a flag for now.

[Kenton Garner]

Noel - I would be in favor of either of those two options. #2 seems reasonable to me, but I understand you may have an historical precedent.

[Thomas Mueller]

Hi,

(1) add a manual command to clean them up

Yes, that would be an option, and we would not have to change behavior by default (which is a risk as it could break existing applications).

 

(2) change it so that the client can only read from a BLOB while a transaction is open. Flush any temporary BLOBs on commit. This is how other databases work, but it would be a change for us. So maybe we would have to hide this option behind a flag for now.

I didn't know that. I made some tests (see below), and at least Apache Derby seems to do that. I'm not sure about other databases however; PostgreSQL doesn't seem to support CLOB(?) and HSQLDB didn't remove the CLOB on commit. That should be possible to implement; we already have an array for LOB entries to remove on commit, in Session.unlinkLobMap. I think adding the LOBs there should work. Yes, we need to be a bit careful with compatibility (with other database and with previous versions of H2); I guess we could change the default behavior in version 1.4.x as it is marked beta.

Test case results:

jdbc:h2:mem:

while reading: aaa

after next: aaa

after result set close: aaa

after delete: aaa

jdbc:hsqldb:/tmp/db/test

while reading: aaa

after next: aaa

after result set close: aaa

after delete: aaa

jdbc:derby:/tmp/db/test2;create=true

while reading: aaa

after next: aaa

after result set close: java.sql.SQLException: You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after the Blob/Clob's transaction has been committed or rolled back.

after delete: java.sql.SQLException: You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after the Blob/Clob's transaction has been committed or rolled back.

Test case:

package db;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.h2.tools.DeleteDbFiles;

public class TestMultiDb {

    public static void main(String[] a) throws Exception {

        DeleteDbFiles.execute("~/temp", "test", true);

//        Server s = Server.createTcpServer().start();

//        try {

//            test("org.h2.Driver", "jdbc:h2:tcp://localhost/~/temp/test", "sa", "sa");

//        } finally {

//            s.stop();

//        }

        test("org.h2.Driver", "jdbc:h2:mem:", "sa", "sa");

        // test("org.h2.Driver", "jdbc:h2:~/temp/test", "sa", "sa");

        test("org.hsqldb.jdbcDriver", "jdbc:hsqldb:/tmp/db/test", "sa", "sa");

        test("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:/tmp/db/test2;create=true", "sa", "sa");

        // test("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/test", "sa", "sa");

        // test("oracle.jdbc.driver.OracleDriver",

        // "jdbc:oracle:thin:@192.168.0.100:1521:XE", "sa", "sa");

        // test("org.postgresql.Driver", "jdbc:postgresql:test", "sa", "sa");

    }

    static void test(String driver, String url, String user, String password) throws Exception {

        System.out.println(url);

        Class.forName(driver);

        Connection conn = DriverManager.getConnection(url, user, password);

        Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

        try {

            stat.execute("drop table test");

        } catch (SQLException e) {

            // ignore

        }

        try {

            stat.execute("create table test(id int primary key, data clob)");

        } catch (SQLException e) {

            stat.execute("create table test(id int primary key, data varchar(255))");

        }

        PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");

        prep.setInt(1, 1);

        prep.setString(2, "aaa" + new String(new char[1024 * 16]).replace((char) 0, 'x'));

        prep.execute();

        prep.setInt(1, 2);

        prep.setString(2, "bbb" + new String(new char[1024 * 16]).replace((char) 0, 'x'));

        prep.execute();

        ResultSet rs = stat.executeQuery("select * from test order by id");

        rs.next();

        Clob c1 = rs.getClob(2);

        System.out.println("while reading: " + c1.getSubString(1, 3));

        rs.next();

        System.out.println("after next: " + c1.getSubString(1, 3));

        rs.close();

        try {

            System.out.println("after result set close: " + c1.getSubString(1, 3));

        } catch (SQLException e) {

            System.out.println("after result set close: " + e);

        }

        stat.execute("delete from test");

        try {

            System.out.println("after delete: " + c1.getSubString(1, 3));

        } catch (SQLException e) {

            System.out.println("after delete: " + e);

        }

        conn.close();

        System.out.println();

    }

}

Regards,

Thomas

[Noel Grandin]

On 2015-03-26 08:48 AM, Thomas Mueller wrote:
>
>
> I didn't know that. I made some tests (see below), and at least Apache Derby seems to do that. I'm not sure about other

In Oracle, you may only read a BLOB column in the same order as the SELECT statement, and the BLOB ceases to be valid as
soon as you read the next column:
http://docs.oracle.com/cd/B28359_01/java.111/b31224/jstreams.htm#BHCDJAGI

Despite a lot of searching, I could not find any information on how this works in Sybase or MS-SQL-Server.

[Thomas Mueller]

Hi,

Hm, interesting, Oracle does it the "easy way" then... I didn't know that. That was similar to the original H2 behavior, and them somebody told me this is wrong (I think it was somebody from the Hibernate team, but I don't remember).

I don't have MySQL installed right now, could anybody test it? If not, I will install the latest version and test later on.

Regards,

Thomas

[Fred&Dani&Pandora&Aquiles]

Hi,

The output for mysql 5.5 is:

jdbc:mysql://localhost:3306/test

while reading: aaa

after next: aaa

after result set close: aaa

after delete: aaa

Regards,

Fred

[Fred&Dani&Pandora&Aquiles]

Hi, 

Sorry, I made a mistake, I tested the mysql with the data column as text. Apparently, the mysql as Postgres, provide the text data type to deal with large strings. So, the previously output is for a text data type. Besides, in the Postgres I'm not able to execute rs.getClob(2).

Regards,

Fred

[Kenton Garner]

SQLServer 2005 if your interested...

jdbc:sqlserver://xxx.xxx.xxx.xxx:1433

while reading: aaa
after next: aaa
after result set close: aaa
after delete: aaa

Uses VARCHAR(MAX) instead of CLOB.

But don't let this result sway you...  I vote for the change. 

The Java docs only say that

"Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created."  

No guarantees after that.

-Kent

On Thursday, March 26, 2015 at 1:00:20 PM UTC-4, Thomas Mueller wrote:

Hi,

Hm, interesting, Oracle does it the "easy way" then... I didn't know that. That was similar to the original H2 behavior, and them somebody told me this is wrong (I think it was somebody from the Hibernate team, but I don't remember).

I don't have MySQL installed right now, could anybody test it? If not, I will install the latest version and test later on.

Regards,

Thomas

On Thursday, March 26, 2015, Noel Grandin <noelg...@gmail.com> wrote:

On 2015-03-26 08:48 AM, Thomas Mueller wrote:

I didn't know that. I made some tests (see below), and at least Apache Derby seems to do that. I'm not sure about other

In Oracle, you may only read a BLOB column in the same order as the SELECT statement, and the BLOB ceases to be valid as soon as you read the next column:
  http://docs.oracle.com/cd/B28359_01/java.111/b31224/jstreams.htm#BHCDJAGI

Despite a lot of searching, I could not find any information on how this works in Sybase or MS-SQL-Server.

--
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+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

This should be fixed in trunk (and the next release).

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Kenton Garner]

Thomas, what did you decide to do for this?

[Fred&Dani&Pandora&Aquiles]

Hi,

I think he added a timeout for BLOB and CLOB objects:

References to BLOB and CLOB objects now have a timeout. The configuration setting is LOB_TIMEOUT (default 5 minutes). This should avoid growing the database file if there are many queries that return BLOB or CLOB objects, and the database is not closed for a longer time.

Regards,

Fred

2015-04-11 16:07 GMT-03:00 Kenton Garner <kenton...@gmail.com>:

Thomas, what did you decide to do for this?

[Thomas Mueller]

Hi,

Yes, I added a timeout. 

First I wanted to keep the CLOB / BLOB objects only until the next transaction. The problem is that some methods in JdbcConnection started a new transaction (Statement.getQueryTimeout for example). It would be better if those transactions are not required, but thats a change for another day.

Regards,

Thomas

[Vitali]

Hello.

I would like to share some   observations.  Recently H2 got a Geometry type, logic around it seems is growing, also some extra tiers like H2GIS are under development.  All together this seems as a future of spatial support in H2.  But already for many years  the spatial support was provided by a combination of geodb + hatbox libraries and integration in GeoTools  world (as  H2 data store  interface for storing/managing spatial features with geometries).
All these was done on BLOB type where a geometry WKB is stored.

BLOB became completely useless as a type for handling WKB of geometries. Because of  this change that any access of BLOB value makes a copy of it. HATBOX and GEODB libs based  on JTS library  provide functions to work with WKB. But any call of these functions makes a read of BLOB value which makes a copy in memory.  Some spatial conflation operations being not-optimized (having polynomial complexity with applying spatial predicates between any combination of input geometries from 2 tables e.g.)  now have a catastrophic performance and memory consumption.  Cases where  old H2 just worked 10 secods performing some kind of spatial operation between 2 layers (tables) now runs 2 hours , 3Gb of database file (instead of 400Mb normally) and outofmemory error finally. And long cleanings of temporary LOB storage on app start, app close, transaction commit after such operations.

I understand real reasons of this BLOB  copying approach.  But the conclusion is that BLOB is not a right type for geometries. In typical GIS (like UDIG) thousands of records are extracted every second for multiple layers during rendering and other types of requests need geometries. Now BLOB became inefficient.

Alltogether very likely I will do refactoring of geodb, hatbox and GeoTools to work with GEOMETRY type which is basically VARBINARY kind of which means WKB is just read to memory. But it is what usually is needed to GIS app - to get a geometry almost every time when data is read. Also because  JTS geometry is lazily cached in ValueGeometry various logic in H2 (like custom spatial functions call multiple times) gets benefits.  I think H2GIS toolkit more or less uses this approach already.

The only concern is that are there any limitations for cases like "lake boundary" that consists from hundreds of  thousands of vertices.. Isn't it  2Gb is a limit for binary types? Then it's fine..  But how do older PageStore and modern MVStore handle this type? Any performance issues?

Vitali.

[Thomas Mueller]

Hi,

> But already for many years  the spatial support was provided by a combination of geodb + hatbox libraries and integration in GeoTools  world

Yes. However, those don't use the built-in R tree. Do they use an external R tree?

> All these was done on BLOB type where a geometry WKB is stored. 

A small BLOB is stored inline, so it might not be that bad.

>  any access of BLOB value makes a copy of it

Access is making a copy of the reference of a large BLOB.

> Isn't it  2Gb is a limit for binary types?

I reality the problem is the memory usage (heap memory).

Regards,

Thomas

[Vitali]

On Monday, May 4, 2015 at 3:32:39 PM UTC+3, Thomas Mueller wrote:

Hi,

> But already for many years  the spatial support was provided by a combination of geodb + hatbox libraries and integration in GeoTools  world

Yes. However, those don't use the built-in R tree. Do they use an external R tree?

Hatbox provides R-Tree.  It is based on H2 infrastructure (some auxiliary table is created where nodes are stored).  What is "built-in" R-Tree would mean?

> All these was done on BLOB type where a geometry WKB is stored. 

A small BLOB is stored inline, so it might not be that bad.

>  any access of BLOB value makes a copy of it

Yes, that is what I meant.  For certain scenarios it has significant performance issues anyway as I experienced. Millions of temporary LOB entries when you have just dozens of thousands of spatial records and some not very optimized spatial query.

Access is making a copy of the reference of a large BLOB.

> Isn't it  2Gb is a limit for binary types?

I reality the problem is the memory usage (heap memory).

That  should not be a problem.  Typically in GIS application the biggest result  sets extracted from the database are not hold or cached long time but rather used to render spatial features and immediately any references released in JVM. Whether it's BLOB or BINARY anyway it's loaded to memory to parse Geometry from WKB.  May be with VARBINARY a  bit more data is kept during short period  of time in memory than would be with BLOBs.  May be I would consider an approach in ValueGeometry  that bytes are kept just until geometry is requested, then lazily Geometry is parsed and bytes are released. So that at any point of time whether bytes are hold or Geometry as an object. From bytes to Geometry, from Geometry to bytes when necessary.

In SELECT scenarious bytes are needed until Geometry object is created and then it is used outside of result set or locally during command execution. I am not sure how relevant in scope of the whole database infrastructure does this sound.
Am I right that until local result set data structure is fully composed it is not returned to caller?  Then if result set is huge then all bytes are anyway kept in memory until result set is delivered and the client starts to request Geometry objects when bytes would be cleaned...


Vitali.

[bocher]

Hi Vitali,

Some comments.

H2GIS is an improvement and refactoring of the H2spatial extension used and developed since 2005 at CNRS. The first release was presented during the GvSIG days in 2006 (https://halshs.archives-ouvertes.fr/halshs-01145771). In fact, the H2 spatial extension was developed to support hydrological spatial analysis methods during my thesis (2002-2005). The first architecture of H2 spatial was very very chaotic :-(. In July 2006, we discover the Chris Holmes (Open plan projet) approach's, available from Derby and HSQL (http://old.geoserver.org/SpatialDBBox.html). The second architecture 2006-2010 follows this approach. A custom blog data type was used to store geometry in H2 database.

In 2011, we decided to contact Thomas to talk with him about spatial index in H2. Thomas was (as usual) very receptive to our needs and he has added a Rtree index storage in H2. Since 2011, we collaborate with the H2 community about the geometry type and a new extension called H2GIS is born.

H2GIS is used in my team to process huge data and create advanced spatial analysis or simulation like noisemap (http://noisemap.orbisgis.org/). For example, we are able to process billion of noise sources located on road network. So yes H2 database is very robust and efficient.

You said « Alltogether very likely I will do refactoring of geodb, hatbox and GeoTools to work with GEOMETRY type... »

I'm working on a extension to connect H2GIS with Geoserver using the Geotools datastore model (https://github.com/ebocher/geoserver-h2gis, thanks to geotools community). May be a good option for you... So you can take profit of all H2GIS functionalities (ST_ConstainedDelunay, Network Analysis, SPHAPEFILE TABLE access...).

My colleague Nicolas Fortin will definitely answer to the technical points (memory usage, data type…).

Best regards

Erwan

[Nicolas Fortin (OrbisGIS)]

Hi Vitali,

PostGIS maximum geometry field size is 1Gb[1], then i don't think its a problem for H2 having a theoretical limit of 2.1 Gb (precisely 2³¹-5 [2]) . It is not very practical to have such huge geometry as it is slow to render and process (no stream processing for vectorial geometries in jts).

As Thomas have said you have now access to native spatial r-tree index with the same syntax as PostGIS (&& operator):
http://www.h2database.com/html/advanced.html#spatial_features

There is just a missing piece with spatial index is the support for sql alias in h2. In order to link && operator with spatial predicates.

I understand if GPLv3 license of H2GIS is a issue for you that you want to update geotools and geodb (you don't need hatbox anymore).

All our 744 units test of H2GIS are running fine with H2 MvStore. We greatly appreciate the power of H2 database :)

Regards,


 [1]:http://www.postgresql.org/about/
 [2]:http://stackoverflow.com/questions/3038392/do-java-arrays-have-a-maximum-size

-- 
Nicolas Fortin
IRSTV FR CNRS 2488
GIS        http://orbisgis.org
Spatial DB http://h2gis.org
Noise      http://noisemap.orbisgis.org

[Vitali]

I agree about H2. It's great database. Only because we found H2  embedded database  with good simple enough spatial capabilities (in our case based on hatbox and geotools) we were able to complete a project for a big customer with 400 of users  managing 25 Gb of business critical spatial data daily!

After going deep to H2 internals and what i have heard from you, guys, we are convinced that h2gis is a way to the future that should give a performance boost comparing to current technology we are using.  I am not a license  guru , we are doing projects based on open source libraries, applications and tools for private customers. The core app is UDIG based  and H2 lives in it as an embedded database. All dependencies and components are commercial-friendly using licenses as LGPL and others, etc.

If license is an issue we can always create something simple as geodb  wrapper   for H2 which is with spatial data type and built spatial index support now. Anyway all profit comes more or less from JTS and it's know fact. Whether it's geodb library or H2GIS - they rely on JTS. You may of course have some advanced computational  functionality, but we need really basics from spatial database. It's not for  analysis , it's for storing and managing spatial features, big amounts of them, all advanced  spatial analysis is anyway done in business logic.

Have you ever tried H2 as a spatial database behind some GIS interface like UDig or other desktop Java GIS app? It is definitely great db with excellent performance.


Coming back to the original topic of this thread I would conclude that BLOB type is absolutely unnecessary for storing geometries. 2Gb question was more like theoretical..

Vitali.

[Nicolas Fortin (OrbisGIS)]

hi,

If license is an issue we can always create something simple as geodb  wrapper

UDig (main application) license is BSD. In order to be able to link with GPL based source code you need to include the GPL as well as the original license, and you need to make it clear that each individual file as a whole is covered by the GPL, as well as BSD for portions of it.

Have you ever tried H2 as a spatial database behind some GIS interface like UDig or other desktop Java GIS app? It is definitely great db with excellent performance.

We develop our own desktop GIS OrbisGIS 5.1 which is working with H2GIS under the hood (or PostGIS) with direct access to SQL. http://www.orbisgis.org/download/

Erwan B. have successfully linked GeoServer with H2GIS too.

Regards,

[Kenton Garner]

Thomas,

I hate to keep coming back to this... But I have built the most recent version 1.4.188 and I have been doing more testing in regards to the continual growth of the database due to the temporary LOB references.

In my case, my database file seems to continue to grow even with your new LOB_TIMEOUT value;  I am continuing to issue the following statements every 10 minutes when the database file exceeds 50MB

SET EXCLUSIVE 1
SET RETENTION_TIME 0
CHECKPOINT_SYNC
SET RETENTION_TIME 45000
SET EXCLUSIVE 0
COMMIT

However it rarely helps to reduce the db size.  In my most recent test the database file had reached 500MB. 
After 30+ minutes of no db activity ( just active connections and the pseudo compact code from above / 10 mins. ) the db size did not change.
After closing the database connections ( and ultimately the database itself )... it took about a minute for the database to completely close and remove the lock file and then the file size reduced 300MB.
When I restarted the database and performed the pseudo compact code above it shrunk to 300K.

This behavior does not seem to correlate well with your comments in this thread and what I would have expected. 
It still seems like a complete shutdown is required to reduce the file size.

At the risk of beating this subject to death - do you have any thoughts on this? 
For the most part, It is the only issue and customer complaint that I get from my users concerning the H2 database implementation that I am using.

-Kent

On Tuesday, April 14, 2015 at 2:12:37 AM UTC-4, Thomas Mueller wrote:

Hi,

Yes, I added a timeout. 

First I wanted to keep the CLOB / BLOB objects only until the next transaction. The problem is that some methods in JdbcConnection started a new transaction (Statement.getQueryTimeout for example). It would be better if those transactions are not required, but thats a change for another day.

Regards,

Thomas

On Sunday, April 12, 2015, Fred&Dani&Pandora&Aquiles <zep...@gmail.com> wrote:

Hi,

I think he added a timeout for BLOB and CLOB objects:

References to BLOB and CLOB objects now have a timeout. The configuration setting is LOB_TIMEOUT (default 5 minutes). This should avoid growing the database file if there are many queries that return BLOB or CLOB objects, and the database is not closed for a longer time.

Regards,

Fred

2015-04-11 16:07 GMT-03:00 Kenton Garner <kenton...@gmail.com>:

Thomas, what did you decide to do for this?

--
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+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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+unsubscribe@googlegroups.com.