Re: Data cache size limit is reached

Thanks for the clarification!

Best regards.

On 22/10/2019 14:24, Fred Toussi via Hsqldb-user wrote:
> Data for CACHED tables is loaded into the memory cache row by row before it can be accessed.
> 
> If you reduce the cache size after inserting the rows, the new size must allow for at least few rows to load.
> 
> I will improve the docs to mention the fact that CACHE SIZE must be larger than your largest row.
> 
> Fred
> 
> On Tue, Oct 22, 2019, at 13:51, [email protected] wrote:
>> Hi.
>>
>> Using version 2.5.0 (also happens with older versions), given a database
>> with:
>> SET FILES CACHE SIZE 100
>> CREATE CACHED TABLE PUBLIC.DATA(VALUE VARCHAR(102400) DEFAULT '')
>>
>> when trying to insert a string with 102400 chars it leads to:
>>
>> java.sql.SQLException: Data cache size limit is reached: 100
>> 	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>> 	at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>> 	at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
>> 	at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
>> 	at org.example.db.CacheSizeLimit.main(CacheSizeLimit.java:23)
>> Caused by: org.hsqldb.HsqlException: Data cache size limit is reached: 100
>> 	at org.hsqldb.error.Error.error(Unknown Source)
>> 	at org.hsqldb.error.Error.error(Unknown Source)
>> 	at org.hsqldb.persist.Cache.put(Unknown Source)
>> 	at org.hsqldb.persist.DataFileCache.add(Unknown Source)
>> 	at org.hsqldb.persist.RowStoreAVLDisk.add(Unknown Source)
>> 	at org.hsqldb.persist.RowStoreAVLDisk.getNewCachedObject(Unknown Source)
>> 	at org.hsqldb.Table.insertSingleRow(Unknown Source)
>> 	at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
>> 	at org.hsqldb.StatementInsert.getResult(Unknown Source)
>> 	at org.hsqldb.StatementDMQL.execute(Unknown Source)
>> 	at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
>> 	at org.hsqldb.Session.execute(Unknown Source)
>> 	... 3 more
>>
>>
>> Trying to insert two strings with 51200 chars each also leads to the
>> exception, on the second insert.
>> If the cache size is increased it works fine in both cases.
>>
>> The documentation of the table types [1] says:
>> Furthermore, only part of the data for each CACHED table is held in
>> memory, allowing tables with more data than can be held in memory.
>>
>> so I assumed that if a row does not fit in the cache it would not be cached.
>>
>> The cache size property [2] does not say that the minimum size depends
>> on or relates to the size of the rows. (I can't find that constraint in
>> other parts of the documentation either.)
>>
>> Am I misunderstanding how the cache works?
>>
>>
>> [1] http://hsqldb.org/doc/guide/management-chapt.html#mtc_table_types
>> [2] http://hsqldb.org/doc/guide/management-chapt.html#N1538F
>>
>> Thanks.
>> Best regards.
>>
>>
>> _______________________________________________
>> Hsqldb-user mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
> 
> 
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>