Replace an open database

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Replace an open database

Wade, William
I have an application file that I'm considering moving to sqlite. Some current behavior is:

1) Only one writing thread for the lifetime of a logical dataset.

2) The writing thread opens the file with exclusive write access (open fails if there is another writer, no other writers allowed, but one writer plus multiple readers is a common situation).

3) The writing thread prefers to delete any existing file. If it can't do that (some readers currently have the file open) it gains an exclusive read/write lock (consistent with no reader has a transaction in progress) and truncates the file to zero length, writes its new header (including his own uuid, indicating that this is logically a new file). When existing readers get around to reading again, they will check that uuid, and handle the change in writers "gracefully."

I'm wondering how to implement that behavior while using sqlite as my application file. I can imagine several solutions, but I'm not sure what pitfalls to look for. In particular, haven't determined how to get sqlite to write-open an existing empty file, as-if it were creating a new database.

Possible solutions:

1) As part of the initial write transaction (at the sqlite client level), use sqlite to remove all of the existing tables. There are a couple of issues with this. If the old file was 200gb, I'd expect sqlite to "waste a lot of time" updating its free-page structures. Also, if the new file only grows to a few megabytes, I'd want the excess space to be recovered. I also want the write-open to succeed if the existing file is corrupted.

2) Implement my own VFS, such that old readers get read (and lock) errors until they open a new connection, while my new writer "sees" this as a new file even if the VFS wasn't able to delete the file at the OS level. Since I'm just getting started with sqlite, I'd rather not have to dive into the low-level guts of implementing a new VFS, if I can help it.

3) After gaining the exclusive lock, truncate the database file (and the write-ahead log or rollback journal), create a new database file (somewhere else, possibly a virtual database). Perform a raw copy of the new virtual database into the old actual database file, toss the virtual database, and then use sqlite to open the old (now properly formatted, almost empty) database file as a writer.

4) Change all readers so that they close/reopen the database at all transaction boundaries (so that when there are no current read transactions, nobody else has the file open). I'm a bit worried about the performance implications of this for small read transactions (although I have not tested this). I can do this for all my existing clients, but I can't do this for somebody using some other sqlite3 client (like the command-line client). Also if some non-sqlite code has the file open without a lock (perhaps somebody is currently copying the file using an os-level command), I'd prefer to make that reader fail, rather delay my writer.

Help please?
Bill

**************************************************************************************
This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited.
**************************************************************************************
_______________________________________________
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: Replace an open database

Dominique Devienne
On Wed, May 27, 2015 at 6:59 PM, Wade, William <[hidden email]> wrote:

> (including his own uuid, indicating that this is logically a new file).
> When existing readers get around to reading again, they will check that
> uuid, and handle the change in writers "gracefully."
>

You can perhaps abuse the Application-Id [1] as your uuid to check. But you
could also store a real uuid in a 1-row well-known table. Just a thought
(on that specific point). --DD

[1] https://www.sqlite.org/pragma.html#pragma_application_id
_______________________________________________
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: Replace an open database

Simon Slavin-3
In reply to this post by Wade, William

On 27 May 2015, at 5:59pm, Wade, William <[hidden email]> wrote:

> I'm wondering how to implement that behavior while using sqlite as my application file. I can imagine several solutions, but I'm not sure what pitfalls to look for. In particular, haven't determined how to get sqlite to write-open an existing empty file, as-if it were creating a new database.

While the things you're worried about make sense for serial files, they don't matter or can't be done for SQLite databases.  So don't worry about them.  Also don't worry about speed of execution until (unless) you find your program is too slow.

> 1) Only one writing thread for the lifetime of a logical dataset.

It would seem that the WAL journal mode would suit you.

> 2) The writing thread opens the file with exclusive write access (open fails if there is another writer, no other writers allowed, but one writer plus multiple readers is a common situation).

Just have all your threads open the file with read/write permission.  SQLite locks the entire database during a transaction, preventing the other threads from doing anything with the database until the transaction is over.  You will want to set a busy-timeout for all your connections of a couple of minutes:

<https://www.sqlite.org/c3ref/busy_timeout.html>

> 3) The writing thread prefers to delete any existing file.

This, of course, you can't do.  You can DELETE FROM all your tables, which is optimised in SQLite to execute much faster than deleting each row separately.  Reader threads which have the file open will just see the same thing (database locked) they'd see for any other type of transaction.

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: Replace an open database

Eduardo Morras-2
In reply to this post by Dominique Devienne
On Wed, 27 May 2015 19:07:50 +0200
Dominique Devienne <[hidden email]> wrote:

> On Wed, May 27, 2015 at 6:59 PM, Wade, William <[hidden email]>
> wrote:
>
> > (including his own uuid, indicating that this is logically a new
> > file). When existing readers get around to reading again, they will
> > check that uuid, and handle the change in writers "gracefully."
> >
>
> You can perhaps abuse the Application-Id [1] as your uuid to check.
> But you could also store a real uuid in a 1-row well-known table.
> Just a thought (on that specific point). --DD

Better user-version pragma[2]. Application-Id identifies your sqlite3 db file with your application using unix file (1) program or similar.

> [1] https://www.sqlite.org/pragma.html#pragma_application_id
https://www.sqlite.org/pragma.html#pragma_schema_version

If your new db has identical schema, don't modify the schema version. If you do so, readers must call sqlite3_reset() and sqlite3_prepare() again.

---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
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: Replace an open database

Dan Kennedy-4
In reply to this post by Wade, William
On 05/27/2015 11:59 PM, Wade, William wrote:

> I have an application file that I'm considering moving to sqlite. Some current behavior is:
>
> 1) Only one writing thread for the lifetime of a logical dataset.
>
> 2) The writing thread opens the file with exclusive write access (open fails if there is another writer, no other writers allowed, but one writer plus multiple readers is a common situation).
>
> 3) The writing thread prefers to delete any existing file. If it can't do that (some readers currently have the file open) it gains an exclusive read/write lock (consistent with no reader has a transaction in progress) and truncates the file to zero length, writes its new header (including his own uuid, indicating that this is logically a new file). When existing readers get around to reading again, they will check that uuid, and handle the change in writers "gracefully."
>
> I'm wondering how to implement that behavior while using sqlite as my application file. I can imagine several solutions, but I'm not sure what pitfalls to look for. In particular, haven't determined how to get sqlite to write-open an existing empty file, as-if it were creating a new database.
>
> Possible solutions:
>
> 1) As part of the initial write transaction (at the sqlite client level), use sqlite to remove all of the existing tables. There are a couple of issues with this. If the old file was 200gb, I'd expect sqlite to "waste a lot of time" updating its free-page structures. Also, if the new file only grows to a few megabytes, I'd want the excess space to be recovered. I also want the write-open to succeed if the existing file is corrupted.

This is by far the best solution. Attempting to manipulate an SQLite
database file using file-system APIs while other clients have it open is
at best an accident waiting to happen. Use SQLite APIs instead.

But instead of using a regular SQL transaction to drop all the old
tables, use the backup API to clobber the existing database with the new
one.

   https://www.sqlite.org/c3ref/backup_finish.html

Using the backup API, the clobber operation is still done as a regular
SQLite transaction - so all the locking and notifying of other clients
gets done right. The amount of IO (and CPU) required should depend on
the size of the new db only, not the existing db size. And it won't
matter if the existing db is corrupt or not - as the backup API never
actually examines the contents of the existing database.

Dan




>
> 2) Implement my own VFS, such that old readers get read (and lock) errors until they open a new connection, while my new writer "sees" this as a new file even if the VFS wasn't able to delete the file at the OS level. Since I'm just getting started with sqlite, I'd rather not have to dive into the low-level guts of implementing a new VFS, if I can help it.
>
> 3) After gaining the exclusive lock, truncate the database file (and the write-ahead log or rollback journal), create a new database file (somewhere else, possibly a virtual database). Perform a raw copy of the new virtual database into the old actual database file, toss the virtual database, and then use sqlite to open the old (now properly formatted, almost empty) database file as a writer.
>
> 4) Change all readers so that they close/reopen the database at all transaction boundaries (so that when there are no current read transactions, nobody else has the file open). I'm a bit worried about the performance implications of this for small read transactions (although I have not tested this). I can do this for all my existing clients, but I can't do this for somebody using some other sqlite3 client (like the command-line client). Also if some non-sqlite code has the file open without a lock (perhaps somebody is currently copying the file using an os-level command), I'd prefer to make that reader fail, rather delay my writer.
>
> Help please?
> Bill
>
> **************************************************************************************
> This e-mail and any attachments thereto may contain confidential information and/or information protected by intellectual property rights for the exclusive attention of the intended addressees named above. If you have received this transmission in error, please immediately notify the sender by return e-mail and delete this message and its attachments. Unauthorized use, copying or further full or partial distribution of this e-mail or its contents is prohibited.
> **************************************************************************************
> _______________________________________________
> 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: Replace an open database

rene
In reply to this post by Wade, William
On 05/27/2015 11:59 PM, Wade, William wrote:

>> 3) The writing thread prefers to delete any existing file. If it can't do that (some readers currently have the file open) it gains an exclusive read/write lock (consistent with no reader has a transaction in progress) and truncates the file to zero length, writes its new header (including his own uuid, indicating that this is logically a new file). When existing readers get around to reading again, they will check that uuid, and handle the change in writers "gracefully."
>>
>
>But instead of using a regular SQL transaction to drop all the old
>tables, use the backup API to clobber the existing database with the new
>one.
>
>   https://www.sqlite.org/c3ref/backup_finish.html
>
>Using the backup API, the clobber operation is still done as a regular
>SQLite transaction - so all the locking and notifying of other clients
>gets done right. The amount of IO (and CPU) required should depend on
>the size of the new db only, not the existing db size. And it won't
>matter if the existing db is corrupt or not - as the backup API never
>actually examines the contents of the existing database.
>
>Dan

Interesting idea. Could this also a solution to my problem described in the thread "emptying tables"?


Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: Replace an open database

Dan Kennedy-4
On 05/29/2015 12:59 PM, Zaumseil René wrote:

> On 05/27/2015 11:59 PM, Wade, William wrote:
>
>>> 3) The writing thread prefers to delete any existing file. If it can't do that (some readers currently have the file open) it gains an exclusive read/write lock (consistent with no reader has a transaction in progress) and truncates the file to zero length, writes its new header (including his own uuid, indicating that this is logically a new file). When existing readers get around to reading again, they will check that uuid, and handle the change in writers "gracefully."
>>>
>> But instead of using a regular SQL transaction to drop all the old
>> tables, use the backup API to clobber the existing database with the new
>> one.
>>
>>    https://www.sqlite.org/c3ref/backup_finish.html
>>
>> Using the backup API, the clobber operation is still done as a regular
>> SQLite transaction - so all the locking and notifying of other clients
>> gets done right. The amount of IO (and CPU) required should depend on
>> the size of the new db only, not the existing db size. And it won't
>> matter if the existing db is corrupt or not - as the backup API never
>> actually examines the contents of the existing database.
>>
>> Dan
> Interesting idea. Could this also a solution to my problem described in the thread "emptying tables"?

Maybe. Using the backup API to clobber one database with another
featuring a bunch of empty tables is likely quicker than actually
deleting all the data from the original.

Dan.




>
>
> Rene
>
> Kernkraftwerk Goesgen-Daeniken AG
> CH-4658 Daeniken, Switzerland
>
> Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
> _______________________________________________
> 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: Replace an open database

Dominique Devienne
On Fri, May 29, 2015 at 8:48 AM, Dan Kennedy <[hidden email]> wrote:

> On 05/29/2015 12:59 PM, Zaumseil René wrote:
>
>> On 05/27/2015 11:59 PM, Wade, William wrote:
>>
>>> But instead of using a regular SQL transaction to drop all the old
>>> tables, use the backup API to clobber the existing database with the new
>>> one.
>>>
>>>    https://www.sqlite.org/c3ref/backup_finish.html
>>>
>>> Using the backup API, the clobber operation is still done as a regular
>>> SQLite transaction - so all the locking and notifying of other clients
>>> gets done right. The amount of IO (and CPU) required should depend on
>>> the size of the new db only, not the existing db size. And it won't
>>> matter if the existing db is corrupt or not - as the backup API never
>>> actually examines the contents of the existing database.
>>>
>> Interesting idea. Could this also a solution to my problem described in
>> the thread "emptying tables"?
>>
>
> Maybe. Using the backup API to clobber one database with another featuring
> a bunch of empty tables is likely quicker than actually deleting all the
> data from the original.


Dan, can you please explain quickly why? Does the backup API work at the
page level for example, and thus is more efficient than normal row level
ops? Thanks, --DD
_______________________________________________
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: Replace an open database

Dan Kennedy-4
On 05/29/2015 02:35 PM, Dominique Devienne wrote:

> On Fri, May 29, 2015 at 8:48 AM, Dan Kennedy <[hidden email]> wrote:
>
>> On 05/29/2015 12:59 PM, Zaumseil René wrote:
>>
>>> On 05/27/2015 11:59 PM, Wade, William wrote:
>>>
>>>> But instead of using a regular SQL transaction to drop all the old
>>>> tables, use the backup API to clobber the existing database with the new
>>>> one.
>>>>
>>>>     https://www.sqlite.org/c3ref/backup_finish.html
>>>>
>>>> Using the backup API, the clobber operation is still done as a regular
>>>> SQLite transaction - so all the locking and notifying of other clients
>>>> gets done right. The amount of IO (and CPU) required should depend on
>>>> the size of the new db only, not the existing db size. And it won't
>>>> matter if the existing db is corrupt or not - as the backup API never
>>>> actually examines the contents of the existing database.
>>>>
>>> Interesting idea. Could this also a solution to my problem described in
>>> the thread "emptying tables"?
>>>
>> Maybe. Using the backup API to clobber one database with another featuring
>> a bunch of empty tables is likely quicker than actually deleting all the
>> data from the original.
>
> Dan, can you please explain quickly why? Does the backup API work at the
> page level for example, and thus is more efficient than normal row level
> ops? Thanks, --DD

That's pretty much it. Backup API just blindly copies pages of data from
the source database into the target. But running "DROP TABLE" or "DELETE
FROM" has to, at a minimum, iterate through the b-tree structures and
add the now unused pages to the database free list.

wrt to this diagram:

   https://www.sqlite.org/arch.html

the backup API works at the Pager level, bypassing B-Tree altogether.

Dan.













> _______________________________________________
> 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: Replace an open database

rene
In reply to this post by Wade, William
>On Fri, May 29, 2015 at 8:48 AM, Dan Kennedy <[hidden email]<http://sqlite.1065341.n5.nabble.com/user/SendEmail.jtp?type=node&node=82464&i=0>> wrote:
>
>> On 05/29/2015 12:59 PM, Zaumseil René wrote:
>>
>>> On 05/27/2015 11:59 PM, Wade, William wrote:
>>>
>>>> But instead of using a regular SQL transaction to drop all the old
>>>> tables, use the backup API to clobber the existing database with the new
>>>> one.
>>>>

I have a database open, statements prepared and data inserted.
Now I clobber these database using the backup API with a new empty one.
The new database has the same structure (table,index).
Can I use the already prepared statements?


Thank you,
Rene


Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
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: Replace an open database

Dan Kennedy-4
On 05/29/2015 03:27 PM, Zaumseil René wrote:

>> On Fri, May 29, 2015 at 8:48 AM, Dan Kennedy <[hidden email]<http://sqlite.1065341.n5.nabble.com/user/SendEmail.jtp?type=node&node=82464&i=0>> wrote:
>>
>>> On 05/29/2015 12:59 PM, Zaumseil René wrote:
>>>
>>>> On 05/27/2015 11:59 PM, Wade, William wrote:
>>>>
>>>>> But instead of using a regular SQL transaction to drop all the old
>>>>> tables, use the backup API to clobber the existing database with the new
>>>>> one.
>>>>>
> I have a database open, statements prepared and data inserted.
> Now I clobber these database using the backup API with a new empty one.
> The new database has the same structure (table,index).
> Can I use the already prepared statements?

If they were compiled with sqlite3_prepare_v2() they will still work. In
this case SQLite will detect that your db schema has "changed" and
automatically reprepare each statement object the first time
sqlite3_step() is called.

If they were compiled with sqlite3_prepare() you will see SQLITE_SCHEMA
errors.

Dan.







>
>
> Thank you,
> Rene
>
>
> Kernkraftwerk Goesgen-Daeniken AG
> CH-4658 Daeniken, Switzerland
>
> Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
> _______________________________________________
> 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