SQLite Forum


7 forum posts by user rogerbinns

13:49 Reply: sqlite3_serialize / deserialize 2GB limit on 64 bit (artifact: 78fedfec0b user: rogerbinns)

That doesn't address the issue. The documentation for sqlite3_serialize, sqlite3_deserialize, and sqlite3_malloc64 do not mention any 2GB limit. In this specific case SQLite is doing a bulk export or import of the database so even the comment in the page you linked isn't obviously pertinent.

In any event can the SQLite team please either document an intended limit of 2GB for serialization, or make it work (eq use a new sqlite3_bulk_alloc with no 2GB limit).


21:34 Post: sqlite3_serialize / deserialize 2GB limit on 64 bit (artifact: 21dafb13aa user: rogerbinns)

The underlying code calls sqlite3_malloc64 which calls sqlite3Malloc. That method fails with allocations above 2GB even on 64 bit platforms. Is the intention to have this undocumented limit to serialize/deserialize?

The code for sqlite3Malloc has this comment:

if( n==0 || n>=0x7fffff00 ){
    /* A memory allocation of a number of bytes which is near the maximum
    ** signed integer value might cause an integer overflow inside of the
    ** xMalloc().  Hence we limit the maximum size to 0x7fffff00, giving
    ** 255 bytes of overhead.  SQLite itself will never use anything near
   ** this amount.  The only way to reach the limit is with sqlite3_malloc() */

SQLite needs this amount and more for serialization and for me to call deserialize and have SQLite free the memory.


20:51 Reply: sqlite3_deserialize double memory freeing (artifact: 0ea5dd117d user: rogerbinns)

I was only doing that to avoid having to open another database, just to check that my serialize/deserialize wrapper works correctly! Now I create the content in temp and deserialize into main. (I also had to rename the table that got copied otherwise some queries claim that the copied table doesn't exist when it clearly does - it seemed like deserialize wasn't setting a schema has changed flag unless I did the spurious modification.)

22:58 Reply: Tips for getting SQLite under Python to perform better with more RAM? (artifact: 533d31e500 user: rogerbinns)

As others mentioned you can use PRAGMA mmap but that will only cover the first 2GB of the database (a 32 bit limitation even on 64 bit platforms imposed by SQLite itself).

The Python GIL is released while SQLite code is running, but SQLite itself then has a per database handle mutex so you won't get concurrency on the SQLite side unless you open the database multiple times. Consequently you'll get best performance if you open the database separately in each thread. (Note: the sqlite3 module does not support cross thread usage of the same db handle.)

The number of different query strings you have will also matter. Under the hood the text query strings get turned into C level sqlite3_stmt which is what does the actual query. The cached_statements parameter to connect controls a cache of text strings to sqlite3_stmt, defaulting to 100. If you have more than 100 then things will go slower (the text to sqlite3_stmt conversion is quite slow).

Beyond this, performance will depend on the usual profiling, your schema, indices etc.

I did implement a tracer in APSW that runs against your unmodified program and can show all queries summarised by popularity, execution time etc - https://rogerbinns.github.io/apsw/execution.html#apsw-trace

22:29 Post: sqlite3_deserialize double memory freeing (artifact: e1cbb5f450 user: rogerbinns)

I'm experiencing a double free in SQLite code on close when using sqlite3_deserialize. The C library complains: free(): double free detected in tcache 2 as does valgrind complain in the sqlite3_close.

Specifically I serialize the main db, and then deserialize it into the temp db. Full code is at https://gist.github.com/rogerbinns/d11994c1d85e36c341e20f25ec491f5e

Extract (leaving out error checking for clarity - error checking is in gist):

/* in memory db with default flags */
res = sqlite3_open_v2("", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

/* create a table so the database is not empty */
res = sqlite3_exec(db, "create table foo(x)", NULL, NULL, NULL);

/* serialize */
serialized = sqlite3_serialize(db, "main", &serial_size, 0);

/* copy the data */
datacopy = sqlite3_malloc64(serial_size);
memcpy(datacopy, serialized, serial_size);

/* free serialized (caller is responsible for freeing the returned value to avoid a memory leak) */

/* now load data into temp */
res = sqlite3_deserialize(db, "temp", datacopy, serial_size, serial_size, SQLITE_DESERIALIZE_RESIZEABLE | SQLITE_DESERIALIZE_FREEONCLOSE);


Any ideas?


21:48 Reply: SQLITE_DETERMINISTIC change in 3.32 (artifact: 552e8e236a user: rogerbinns)
> CREATE INDEX t2a ON t2(a) WHERE deterministic();      -- works

I've updated my test to use this.  It meets the goal of verifying my wrapper really is setting the flag correctly.


18:33 Post: SQLITE_DETERMINISTIC change in 3.32 (artifact: bc6ed1f746 user: rogerbinns)
In my test suite I register a deterministic function of the same name.  In prior releases this returned 1, and now it returns 0:

    SELECT deterministic() == deterministic()

I have confirmed that the registration is correct, and that the function is called twice for this SQL.  Using explain shows two calls, while with 3.31 it shows only one.

Is this intended, and is it a permanent change in SQLite?