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

[Paul]

> 
> I want to confirm that issue is fixed for me.
> Thanks again, Dan!
> 
> 
> Please ignore this update, patch fixes this problem as well.
> 
> > 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...
> > 



At the end of the day my head was so big that I, having some ten different test 
cases,
have errourneously confirmed that 'INSERT OR REPLACE' is fixed also, by running 
wrong test.
But sadly it isn't. Here, I'll drop my test program again, for clarity.

Thanks and sorry for confusion.


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