Remove row to insert new one on a full database

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Remove row to insert new one on a full database

Arthur Blondel
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Chris Locke-3
> When the database is full

What do you mean by a full database?  Do you mean when the operating system
has run out of disk space?
A SQLite database can hold millions of rows, so technically, a database
cannot be 'full'.

It would be easier explaining the full issue and what you consider the
problem, rather than asking for help on a solution which may not be
required.


Thanks,
Chris

On Thu, Apr 4, 2019 at 11:53 AM Arthur Blondel <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Roger Schlueter
In reply to this post by Arthur Blondel
This looks to be an example of the classic XY Problem.  You are asking
how to solve Problem X when what you're trying to do is solve Problem
Y.  In this case, "X" is a full database, which is almost certainly an
oxymoron since SQLIte can store millions of rows of data.  It is not
clear what Problem Y really is.

How do you know the database is "full"?  What does the inserted data
being the "same size" mean?  More generally, what are you trying to do,
how much and what kind of data are you inserting and what platform and
SQLite version are you using?

On 4/3/2019 23:07, Arthur Blondel 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Richard Damon
In reply to this post by Arthur Blondel
On 4/4/19 2:07 AM, Arthur Blondel 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

As people say, this isn't a good description of the problem, and I
suspect that your statement of 'same size' is a big part of the issue.
Did you realize that the size of a row can be affected by the values
being inserted into it? This means that if you do have an upper limit to
the size of the database, and need to delete some data to make room for
more, if you don't vacuum the database to squeeze out  the holes in the
database, you will need to delete a row that is big enough to store the
new row to have room to store it. After that, you may have room to store
a number of new rows that fit within the gaps you left behind.

Vacuuming a database can be a slow operation, because it basically needs
to copy the whole database into a new copy, squeezing out the gaps as it
goes. It also says you need space on your system for the two copies of
the database, so if that is the critical issue, might not be feasible.
Vacuuming, if practical, is the best way to (after you delete something)
to make room in the database, as it can bring together all the odd holes
from the various pieces of deleted data. 

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Stephen Chrzanowski
In reply to this post by Arthur Blondel
This almost sounds like "Full" is a software limitation, in that your
application is specifying that "Full" means you can only have "X" number of
rows.

If you're looking to remove data, I'd suggest that you find some way to
isolate the oldest record, either by a row identifier (Like an ID field
that's using auto-increment) or a date/time stamp (Assigned by
current_timestamp).

Also, your logic is backwards in your pseudo-code.  You should check the
status of the database before you do any kind of insert. The reason is, if
you insert into an already full database, then you're database is over-full
at that point.  Also, your pseudo-code has two conditions to look at...  Do
this forever, and repeat while status is full.  Not to mention, if your
database is messed up and nothing can be inserted even though the table is
empty, you've introduced a lockup.

What I think you're looking more for is:

while (dbStatus() == full) {
  remove_one_row_from_db();
}
result=insert_1_row_to_db();
if (result != resOK) {
  die("uhh.. Problem with the database?");
}


On Thu, Apr 4, 2019 at 6:53 AM Arthur Blondel <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Richard Damon
I think is logic is to attempt to insert a row, and if rather than
inserting it, the call returns the error condition, 'Database Full', you
remove a record and then try again (a form of error recovery), if it
succeeds, then you go on and get more data.

If full was X records, then they would ALWAYS get room to add a new
record after deleting one. Since the reported issue is that it can
require the deletion of possibly very many records to get space to
succeed tells me that 'full' is size based (maybe a max number of
pages). If the record being added is on the larger size of all the 'same
sized' records, then you need to keep deleting records until you get one
that big, or delete two that are consecutive so that you get a large
enough space, or you delete a records that has some spare space next to
it giving enough room.

One issue with this definition is that you can't tell if the database is
currently full except by trying to add the record (or knowing a LOT of
internal details of record storage), which is what the code is doing.

On 4/4/19 9:19 AM, Stephen Chrzanowski wrote:

> This almost sounds like "Full" is a software limitation, in that your
> application is specifying that "Full" means you can only have "X" number of
> rows.
>
> If you're looking to remove data, I'd suggest that you find some way to
> isolate the oldest record, either by a row identifier (Like an ID field
> that's using auto-increment) or a date/time stamp (Assigned by
> current_timestamp).
>
> Also, your logic is backwards in your pseudo-code.  You should check the
> status of the database before you do any kind of insert. The reason is, if
> you insert into an already full database, then you're database is over-full
> at that point.  Also, your pseudo-code has two conditions to look at...  Do
> this forever, and repeat while status is full.  Not to mention, if your
> database is messed up and nothing can be inserted even though the table is
> empty, you've introduced a lockup.
>
> What I think you're looking more for is:
>
> while (dbStatus() == full) {
>   remove_one_row_from_db();
> }
> result=insert_1_row_to_db();
> if (result != resOK) {
>   die("uhh.. Problem with the database?");
> }
>
>
> On Thu, Apr 4, 2019 at 6:53 AM Arthur Blondel <[hidden email]>
> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Simon Slavin-3
On 5 Apr 2019, at 4:14am, Richard Damon <[hidden email]> wrote:

> I think is logic is to attempt to insert a row, and if rather than
> inserting it, the call returns the error condition, 'Database Full'

Okay.  So now we understand what OP meant by the database being full.

SQLITE_FULL does not mean 'Database Full'.  What's full is the volume the database is stored on.  And the problem is worse than it might appear because if the volume is full you can't reliably do anything to the database.  Because even if your next SQL command is DELETE, the first thing that'll happen is that SQLite will try to write to the journal file.  And that may fail, because there's no room for the journal file to get bigger.

In other words, if your database ever gets this big, you need a human to come sort things out.

So don't do that.  Don't let your database get that big.  Monitor the free space and start deleting stuff if free space gets below a certain amount.  Leave yourself 5Meg of space free or something.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Richard Damon
On 4/4/19 11:35 PM, Simon Slavin wrote:

> On 5 Apr 2019, at 4:14am, Richard Damon <[hidden email]> wrote:
>
>> I think is logic is to attempt to insert a row, and if rather than
>> inserting it, the call returns the error condition, 'Database Full'
> Okay.  So now we understand what OP meant by the database being full.
>
> SQLITE_FULL does not mean 'Database Full'.  What's full is the volume the database is stored on.  And the problem is worse than it might appear because if the volume is full you can't reliably do anything to the database.  Because even if your next SQL command is DELETE, the first thing that'll happen is that SQLite will try to write to the journal file.  And that may fail, because there's no room for the journal file to get bigger.
>
> In other words, if your database ever gets this big, you need a human to come sort things out.
>
> So don't do that.  Don't let your database get that big.  Monitor the free space and start deleting stuff if free space gets below a certain amount.  Leave yourself 5Meg of space free or something.
>
> Simon.
Yes, if the error is the SQLite error from the OS saying the disk is
full, then you are in big trouble as you can't expect to be able to do
anything. If perhaps you have some hooks at the file system level that
return that error if this one file hits a certain size and doesn't allow
it to get bigger, but there is still space for the journal file, you
might get the sort of behavior described.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Arthur Blondel
In reply to this post by Arthur Blondel
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 <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Chris Locke-3
Arthur - are you running SQLite in parallel runs?
If you access the database file using the sqlite3 command-line tool, and
try to execute the same SQL commands, do you get the same error ?

SQLite makes a temporary 'journal' file while it's working.  I think that,
on your platform, by default it will be in the same directory as the
database file.  Does your application have enough privileges to create new
files in that directory ?
What version of SQLite are you using?  It might be an old version.
Also, removing rows doesn't necessarily remove space in the database file.
If you're running out of disk space, it could be you need to vacuum your
database file.  How large is the database?  How much disk space do you have
left?


Thanks,
Chris

On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel <[hidden email]>
wrote:

> 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 <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

David Raymond
In reply to this post by Arthur Blondel
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:[hidden email]] On Behalf Of Arthur Blondel
Sent: Friday, April 05, 2019 8:45 AM
To: [hidden email]
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 <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Simon Slavin-3
In reply to this post by Arthur Blondel
On 5 Apr 2019, at 1:45pm, Arthur Blondel <[hidden email]> wrote:

> 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

If SQLite returns SQLITE_FULL you cannot reliably do anything else to the database.  Because even if your next command is DELETE, SQLite needs to temporarily use /more/ disk space for the journal until that transaction is committed.  So your DELETE command can fail too.

Do not use this behaviour.  Instead monitor the free space on the database volume and delete rows when you have only a little space free.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Arthur Blondel
In reply to this post by Chris Locke-3
I have enough disk space. I just limit the database file size artificially
for testing purpose as you can see.
There is no problem of privilege and there is nothing else than the code I
sent. No other access to the DB.
I'm using sqlite 3.16.2

On Fri, Apr 5, 2019 at 3:59 PM Chris Locke <[hidden email]> wrote:

> Arthur - are you running SQLite in parallel runs?
> If you access the database file using the sqlite3 command-line tool, and
> try to execute the same SQL commands, do you get the same error ?
>
> SQLite makes a temporary 'journal' file while it's working.  I think that,
> on your platform, by default it will be in the same directory as the
> database file.  Does your application have enough privileges to create new
> files in that directory ?
> What version of SQLite are you using?  It might be an old version.
> Also, removing rows doesn't necessarily remove space in the database file.
> If you're running out of disk space, it could be you need to vacuum your
> database file.  How large is the database?  How much disk space do you have
> left?
>
>
> Thanks,
> Chris
>
> On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel <[hidden email]>
> wrote:
>
> > 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 <[hidden email]>
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Richard Damon
On 4/5/19 11:14 AM, Arthur Blondel wrote:
> I have enough disk space. I just limit the database file size artificially
> for testing purpose as you can see.
> There is no problem of privilege and there is nothing else than the code I
> sent. No other access to the DB.
> I'm using sqlite 3.16.2

As has been pointed out, one issue is that not all records, even if the
'same' take the same space, so deleting one record may not make enough
room for another.

Another issue is that for indexes, not all free space are the same,
indexes keep similar values together in the index, so adding a row may
need to find related space for an index, or you need to delete enough
rows to either open space where needed or to free a full page of the
index to let that page be used in the newly needed space for the index.

I will admit that these are in a way esoteric implementation dependent
details, so might not seem obvious, but they do explain the
'strangeness' that you see. Many data structures when running at a
capacity limit can demonstrate these sorts of strangeness.

Trying to tightly control resource usage is a tricky problem, and
sometimes you need to think carefully about what you goal actually is
(not what solution you think will work). Putting your hard limit on the
base size of the database does put a hard limit on the size (in bytes)
of the database, but may provide an unexpectedly low capacity of records
in the worse case (and running data structures at this sort of limit
tends to create at times conditions close to worse case), at the cost
that the time to insert a record can grow significantly. If you really
have plenty of disk space, than establishing a record limit in the
database, and when you are at it removing one record for every record
added, will smooth out the access time, at the cost of possibly higher
disk usage at times (but maybe a better ratio of size per records).

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Ling, Andy
> On 4/5/19 11:14 AM, Arthur Blondel wrote:
> > I have enough disk space. I just limit the database file size artificially
> > for testing purpose as you can see.
> > There is no problem of privilege and there is nothing else than the code I
> > sent. No other access to the DB.
> > I'm using sqlite 3.16.2
>
> As has been pointed out, one issue is that not all records, even if the
> 'same' take the same space, so deleting one record may not make enough
> room for another.
>

And I would also point out, the data for each row is not the same.
The id is incrementing. So id 4000 may not be able to be put on the
same page as the id 1 you have just deleted.

Andy Ling


**********************************************************************
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

James K. Lowden
In reply to this post by Arthur Blondel
On Fri, 5 Apr 2019 15:45:10 +0300
Arthur Blondel <[hidden email]> wrote:

> 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.

SQLite *could* avoid that problem by pre-allocating space in the
journal sufficient to permit a single row to be deleted.  But it's not
obvious to me that the complexity is worth it, given the size of disks
these days and consequent rarity of the problem.  

If I were in your shoes, I'd consider maintaining a "dummy" file that's
expendable in the event of a SQLITE_FULL error.  

Compute how much space SQLite needs to delete a row.  Maybe double that
for safety's sake. Create a file that size, and fill it with deadbeef
just to be sure.  Write functions to create and delete that file,
because you'll want to do it consistently.  

When you encounter SQLITE_FULL, delete the file, do the deed, and
re-create the file.  If you can't recreate the file, you have an
unrecoverable error, but an intact database.  

It's not a perfect solution.  To guard against other processes seizing
the space while you're trying to use it, you'd have to wall off the
space, maybe with a loopback filesystem.  But it'd get you further down
the road than you are now.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Richard Damon
On Apr 5, 2019, at 12:31 PM, James K. Lowden <[hidden email]> wrote:

>
> On Fri, 5 Apr 2019 15:45:10 +0300
> Arthur Blondel <[hidden email]> wrote:
>
>> 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.
>
> SQLite *could* avoid that problem by pre-allocating space in the
> journal sufficient to permit a single row to be deleted.  But it's not
> obvious to me that the complexity is worth it, given the size of disks
> these days and consequent rarity of the problem.  
>
> If I were in your shoes, I'd consider maintaining a "dummy" file that's
> expendable in the event of a SQLITE_FULL error.  
>
> Compute how much space SQLite needs to delete a row.  Maybe double that
> for safety's sake. Create a file that size, and fill it with deadbeef
> just to be sure.  Write functions to create and delete that file,
> because you'll want to do it consistently.  
>
> When you encounter SQLITE_FULL, delete the file, do the deed, and
> re-create the file.  If you can't recreate the file, you have an
> unrecoverable error, but an intact database.  
>
> It's not a perfect solution.  To guard against other processes seizing
> the space while you're trying to use it, you'd have to wall off the
> space, maybe with a loopback filesystem.  But it'd get you further down
> the road than you are now.  
>
> --jkl
>
First, the OP has indicated that the FULL message isn’t because the disk is out of space, but they have done something to put a hard limit on the size of the database, so there is room to create the journal to delete the row as there is room for the journal file.

Second, I am not sure SQLite can now exactly how much space will be needed to delete any arbitrary row in the database (or at least be able to figure it out cheaply). Remember it needs to save everything that is going to be changed, including the indexes.

Also, deleting one row may not actually free up any useful space, as has been shown, though if you can delete one row, you could commit that transaction and then delete another (though I can’t be sure if there could be a corner case where deleting a row might increase the size of the database, maybe some trigger fires???)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Remove row to insert new one on a full database

Don Walsh
In reply to this post by James K. Lowden
Stop

On Fri, Apr 5, 2019, 11:31 AM James K. Lowden <[hidden email]>
wrote:

> On Fri, 5 Apr 2019 15:45:10 +0300
> Arthur Blondel <[hidden email]> wrote:
>
> > 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.
>
> SQLite *could* avoid that problem by pre-allocating space in the
> journal sufficient to permit a single row to be deleted.  But it's not
> obvious to me that the complexity is worth it, given the size of disks
> these days and consequent rarity of the problem.
>
> If I were in your shoes, I'd consider maintaining a "dummy" file that's
> expendable in the event of a SQLITE_FULL error.
>
> Compute how much space SQLite needs to delete a row.  Maybe double that
> for safety's sake. Create a file that size, and fill it with deadbeef
> just to be sure.  Write functions to create and delete that file,
> because you'll want to do it consistently.
>
> When you encounter SQLITE_FULL, delete the file, do the deed, and
> re-create the file.  If you can't recreate the file, you have an
> unrecoverable error, but an intact database.
>
> It's not a perfect solution.  To guard against other processes seizing
> the space while you're trying to use it, you'd have to wall off the
> space, maybe with a loopback filesystem.  But it'd get you further down
> the road than you are now.
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Remove row to insert new one on a full database

Hick Gunter
In reply to this post by Arthur Blondel
First, your rows are NOT "all the same size". SQLite uses a compressed format to store values that, among other things, uses less space for smaller integers.

IIRC your records will be (values before colon are the "manifest", values after the colon are the "payload" of a record):
(value is 0) (value is 1) (value is INT) (value is INT) : (2) (3) = 6 bytes in length
(value is 1) (value is 1) (value is INT) (value is INT) : (2) (3) = 6 bytes in length
(value is INT) (value is 1) (value is INT) (value is INT) : (2) (2) (3) = 7 bytes in length
...
(value is INT) (value is 1) (value is INT) (value is INT) : (127) (2) (3) = 7 bytes in length
(value is INT) (value is 1) (value is INT) (value is INT) : (127) (2) (3) = 8 bytes in length

Second, a BTree stores records in the leaf pages only, in sort order and so that the last record of any page sorts before the first record of the next page. Thus the "parent" page only needs to store the first key of it's "child" pages. When a page is completely filled up, it needs to be split. If sorted inserts (like you are performing) are detected, literature recommends splitting 90/10 so the resulting pages willl be 90% full; random inserts result in 50/50 splits, since it is equally likely which side of the spilt the next insert is going to be.

Deleting a record only allows space to be re-used in a limited number of cases:
a) the record to be inserted "belongs" on the same page as the record deleted
b) the record to be deleted is the last one on a leaf page (and BTree software is set up to re-use leaf pages automatically)
c) the record to be deleted causes an internal page to become empty (and BTree Software is set up to re-use internal pages automatically)
d) the record causes 2 adjacent pages to hold less records than would fit into 1 page (and BTree Software is set up to merge adjacent pages automatically)
e) the BTree structure is "defragmented" by whatever method implemented (implicit reload and/or cases b to d)

With your algorithm of deleting the oldest record(s) to make space for new records at "the other end" of the list, you are excluding case a and relying on some combination of b thru d for re-use of pages.

Similar considerations apply to the pages holding the index created by the PRIMARY KEY clause, but the key records are smaller and data is stored in internal nodes, so the effects are smaller..

Note the pattern:

- After the file is "full", you need a new page every 84 records (this will change when your test exceeds 32k records)
- The number of records required to free a page drops from 109 (#of records on first page) to 84 (#of records on sixth thru last used page)

With a "fan out" of 84, your BTree probably has a leaf level of 60 pages of 84 records each, plus 1 internal level of 1 node holding 60 pointers to leaf pages.

>Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Arthur Blondel
>...
>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.
>...
>Following the output:
>
>...
>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
>...


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users