Ignoring for the moment the issues of journals, logs, etc. When you delete a record you free up the space it used *on the page that it was on* (well, pages if there are indexes) Each page is part of the overall B-tree layout of the table/index. When you go to add a new record, if it doesn't belong on the page you just made some room on, then it will look for room on the page it does belong on, or create a new page to put it on.
Oversimplified example: If you have a full phone book and get an error trying to add someone with a last name beginning with "B" you can't just remove the line of someone with a last name beginning with "H". That page is still full of "H"'s and won't let you add a "B" in there completely out of order. You would have to remove all of the names on the "H" page before it would become re-usable for "B" names. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Arthur Blondel Sent: Friday, April 05, 2019 8:45 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Remove row to insert new one on a full database OK, I wasn't clear. I'm limited in space so when the DB is full (when sqlite3_exec() returns SQLITE_FULL when I try to insert a new row), I remove the oldest row and retry to insert the new one. The data is always the same. That's why removing one row should be enough to insert a new one. My problem is that some times I need to remove many rows to add one new one. This is basically my code: main() { sqlite3* db; int rc; char *err_msg = 0; int counter; bool full = false; int id; /* --- Create DB --- */ rc = sqlite3_open("db_file.db", &db); printf("1. rc = %d\n", rc); rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS data_table" "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2 INTEGER, col3 INTEGER)", 0, 0, &err_msg); printf("2. rc = %d\n", rc); /* --- Limit database size to 50 K --- */ rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg); printf("3. rc = %d\n", rc); rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg); printf("4. rc = %d\n", rc); rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file printf("5. rc = %d\n", rc); /* --- Fill DB --- */ for (int i = 0 ; i < 5000 ; i++) { counter = 0; do { rc = sqlite3_exec(db, "INSERT INTO data_table" "(col1, col2, col3) VALUES(1, 2, 3)", 0, 0, &err_msg); if (rc == SQLITE_FULL) { if (!full) { printf("%d - DB full\n", id); full = true; } counter++; // delete oldest row int stat = sqlite3_exec(db, "DELETE FROM data_table WHERE id IN " "(SELECT id FROM data_table ORDER BY id LIMIT 1)", 0, 0, &err_msg); if (stat != SQLITE_OK) { printf("Delete error %d\n", stat); } } else if (rc == SQLITE_OK) { id = sqlite3_last_insert_rowid(db); } else /*if (rc != SQLITE_OK)*/ { printf("Insert error %d\n", rc); } } while (rc == SQLITE_FULL); if (counter > 2) { printf("%d - %d rows was removed\n", id, counter); } } printf("close -> %d\n", sqlite3_close(db)); } Following the output: 1. rc = 0 2. rc = 0 3. rc = 0 4. rc = 0 5. rc = 0 3959 - DB full 3960 - 109 rows was removed 4044 - 92 rows was removed 4128 - 86 rows was removed 4212 - 85 rows was removed 4296 - 85 rows was removed 4380 - 84 rows was removed 4464 - 84 rows was removed 4548 - 84 rows was removed 4632 - 84 rows was removed 4716 - 84 rows was removed 4800 - 84 rows was removed 4884 - 84 rows was removed 4968 - 84 rows was removed close -> 0 Thanks On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel <arthur5blon...@gmail.com> wrote: > > Hello > > When I try to insert new data to a full SQLite database, I need to remove > much more than really needed. I'm doing the following: > > while(1) { > do { > status = insert_1_row_to_db(); > if (status == full) { > remove_one_row_from_db(); > } > } while (status == full);} > > The inserted data has always the same size. When the database is full, > removing only one row is enough to insert the new one. But after a while, I > need to remove 30, 40 and even more the 100 rows to be able to insert one > new row. Is it the correct behavior of SQLite? Is there a way to remove > only what is needed and no more? Thanks > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
