SQLite Forum

How to impose soft_heap method to limit database to 5GB in sqlite

How to impose soft_heap method to limit database to 5GB in sqlite

(1) By ltts_guest (lttsguest) on 2020-11-30 06:46:47 [link] [source]

So i defined a SOFT_HEAP_LIMIT to 5mb and then called sqlite3_soft_heap_limit64(SOFT_HEAP_LIMIT) method while creating the table.

Code Snippet :

static const sqlite3_int64 SOFT_HEAP_LIMIT = 5 * 1024 * 1024; //5mb

int rc = sqlite3_soft_heap_limit64(SOFT_HEAP_LIMIT);

if( rc != SQLITE_OK ){ fprintf(stderr, "SQL error: %sn", zErrMsg); sqlite3_free(zErrMsg); }

I inserted records more than 5mb, but i still do not get "SQLITE_FULL" error. Please let me know what can i do to set the limit and trigger an error when it exceeds that limit?

(2.1) By Keith Medcalf (kmedcalf) on 2020-11-30 08:46:54 edited from 2.0 in reply to 1 [link] [source]

SOFT_HEAP_LIMIT does not limit the size of the database. The SOFT_HEAP_LIMIT sets the heap size limit (MEMORY SIZE) at which the heap manager will attempt to free up unneeded heap pages (for example, clean cache pages). If there are no unused heap pages to free up, then the limit has no effect (this is the meaning of the word SOFT -- if it were HARD then the program would ABEND with an Out of Memory error).

The size of the database is set using pragma max_page_limit=<value> where value is the number of pages.

Assuming that the value returned by executing the statement pragma page_size; is 4096, then you set the maximum database size to 5*1024*1024*1024/4096 -> 1310720 by issuing the command pragma max_page_count=1310720; and ensuring that the value returned when you execute that statement is 1310720. If the database contains more than 1310720 pages at the time you execute the command, then the limit will be set to the current number of pages -- which can be determined by executing the command pragma page_count; which returns the current count of pages comprising the database.

Mutatis Mutandis for page_sizes other than 4096 bytes.

Note that if you wish any of the aforementioned pragma statements to apply to a database schema other than the "main" schema of the connection against which the pragma command is issued, you must specify the schema name to which you would like the command to apply.

Edited to reflect a database size of 5 GB as per the title and not 5 MB as per the code

(3) By Heribert (derhexer) on 2020-11-30 13:42:24 in reply to 2.1 [link] [source]

BTW: What happens if the current page count has reached the max_page_limit and i attempt to insert a new row?
Is there a way to check, if this 'high water mark' is reached (so that i just replace the oldest row insteed of adding a new one)?

(4) By Keith Medcalf (kmedcalf) on 2020-12-01 01:18:16 in reply to 3 [source]

No. Nor is there a way to just "replace an existing row". Setting a max_page_count limits the number of pages that can exist in the database schema when accessed through this connection and has nothing to do with anything else.

That is, even if you delete all the rows and add a new row (or replace a row) there is absolutely no guarantee that for reasons "beyond your ken or control" a new page will not need to be allocated to contain the data, thus causing a violation of the max_page_count limit and causing an error return due to a restriction imposed by max_page_count.

In other words, the way to know that the max_page_count will be exceeded by an operation is to do the operation and, if the max_page_count limit is exceeded, then the operation will be aborted and an error returned.

What you decide to do at that point is up to you.