Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

[Paul]

I want to add even more input for this issue.
I understand why there is implicit savepoint, when I remove row from 'parent' 
table.
But why is this also true for a 'child' table when I perform 'INSERT OR 
REPLACE'?
Removing FK reference disables journal growth. I don't understand...

I have a new test application.

As before, to compile: 
# clang -o test -L/usr/local/lib -lsqlite3 test.c

Still, I do not understand, why does extra transaction (nested savepoint) 
matters.

test.c

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <unistd.h>
#include <string.h>

int main(int argc, char ** argv)
{
    const char * database_file = "/tmp/memusage_test_db";

    // Clear old database file is there is one.
    unlink(database_file);

    sqlite3 * db = NULL;
    if (sqlite3_open(database_file, &db) != SQLITE_OK)
        return 1;

    // Set busy timeout just in case...
    if (sqlite3_busy_timeout(db, 10000) != SQLITE_OK) {
        sqlite3_close(db);
        return 1;
    }

    // Set pragmas.
    if (sqlite3_exec(db,
                     " PRAGMA page_size = 4096;"
                     " PRAGMA temp_store = MEMORY;"
                     " PRAGMA journal_mode = MEMORY;"
                     " PRAGMA cache_size = 10000;"
                     " PRAGMA foreign_keys = ON;"
                     " PRAGMA synchronous = OFF;",
                     NULL, NULL, NULL) != SQLITE_OK) {
        sqlite3_close(db);
        return 1;
    }

    // Create database structure.
    if (sqlite3_exec(db,
                     "CREATE TABLE foo ("
                     "  id  INTEGER,"
                     "  x   INTEGER,"
                     "  PRIMARY KEY(id)"
                     ");"
                     ""
                     "CREATE TABLE bar ("
                     "  id  INTEGER,"
                     "  y   INTEGER,"
                     "  PRIMARY KEY(id, y),"
                     "  FOREIGN KEY(id) REFERENCES foo(id) ON DELETE CASCADE"
                     ");",
                     NULL, NULL, NULL) != SQLITE_OK) {
        sqlite3_close(db);
        return 1;
    }

    // Populate database with data.
    {
        // Open transaction.
        if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != 
SQLITE_OK) {
            sqlite3_close(db);
            return 1;
        }

        if (sqlite3_exec(db, "SAVEPOINT XXX;", NULL, NULL, NULL) != SQLITE_OK) {
            sqlite3_close(db);
            return 1;
        }

        char buffer[256];
        for (int i = 0; i < 100000; ++i) {

            snprintf(buffer, sizeof(buffer), "INSERT INTO foo(id, x) VALUES(%u, 
%u)", i + 1, 2 * i + 1);
            if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
                sqlite3_close(db);
                return 1;
            }

#ifndef NO_CHILD_RECORDS
            snprintf(buffer, sizeof(buffer), "INSERT OR REPLACE INTO bar(id, y) 
VALUES(%u, %u)", i + 1, 2 * i + 1);
            if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
                sqlite3_close(db);
                return 1;
            }
#endif

        }

        // Rollback savepoint (no need to commit anything).
        if (sqlite3_exec(db, "ROLLBACK TO SAVEPOINT XXX;", NULL, NULL, NULL) != 
SQLITE_OK) {
            sqlite3_close(db);
            return 1;
        }

        // Commit transaction.
        if (sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL) != SQLITE_OK) {
            sqlite3_close(db);
            return 1;
        }
    }

    sqlite3_close(db);

    // Dump memory usage statistics.
    int current_mem = 0;
    int hi_mem = 0;
    sqlite3_status(SQLITE_STATUS_MEMORY_USED, &current_mem, &hi_mem, 0);

    printf("Current mem: %u\nHi mem: %u\n", current_mem, hi_mem);

    // Do not leave database file behind.
    unlink(database_file);

    return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users