Re: Data cache size limit is reached

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
>