Re: [sqlite] Remove row to insert new one on a full database
Stop On Fri, Apr 5, 2019, 11:31 AM James K. Lowden wrote: > On Fri, 5 Apr 2019 15:45:10 +0300 > Arthur Blondel 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 > 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
Re: [sqlite] Remove row to insert new one on a full database
On Apr 5, 2019, at 12:31 PM, James K. Lowden wrote: > > On Fri, 5 Apr 2019 15:45:10 +0300 > Arthur Blondel 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
On Fri, 5 Apr 2019 15:45:10 +0300 Arthur Blondel 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
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 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
> 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", );
> > 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, _msg);
> > printf("2. rc = %d\n", rc);
> >
> > /* --- Limit database size to 50 K --- */
> > rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
> > printf("3. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
> > printf("4. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "VACUUM", 0, 0, _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, _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, _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
> > 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
Re: [sqlite] Remove row to insert new one on a full database
On 5 Apr 2019, at 1:45pm, Arthur Blondel 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
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", );
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, _msg);
printf("2. rc = %d\n", rc);
/* --- Limit database size to 50 K --- */
rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
printf("3. rc = %d\n", rc);
rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
printf("4. rc = %d\n", rc);
rc = sqlite3_exec(db, "VACUUM", 0, 0, _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, _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, _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
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
Re: [sqlite] Remove row to insert new one on a full database
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
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", );
> 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, _msg);
> printf("2. rc = %d\n", rc);
>
> /* --- Limit database size to 50 K --- */
> rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
> printf("3. rc = %d\n", rc);
> rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
> printf("4. rc = %d\n", rc);
> rc = sqlite3_exec(db, "VACUUM", 0, 0, _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, _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, _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
> 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
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", );
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, _msg);
printf("2. rc = %d\n", rc);
/* --- Limit database size to 50 K --- */
rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, _msg);
printf("3. rc = %d\n", rc);
rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, _msg);
printf("4. rc = %d\n", rc);
rc = sqlite3_exec(db, "VACUUM", 0, 0, _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, _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, _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
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
Re: [sqlite] Remove row to insert new one on a full database
On 4/4/19 11:35 PM, Simon Slavin wrote: > On 5 Apr 2019, at 4:14am, Richard Damon 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
On 5 Apr 2019, at 4:14am, Richard Damon 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
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
> 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
--
Richard Damon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
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
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
Re: [sqlite] Remove row to insert new one on a full database
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
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
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
Re: [sqlite] Remove row to insert new one on a full database
> 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
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
[sqlite] Remove row to insert new one on a full database
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
