Re: sqlite3 usage for multisession variable storage

[Lars Ingebrigtsen]

Robin Tarsiger <rtt@dasyatidae.com> writes:

> I'm curious about some of these pragma settings more generally.
>
>   - Why journal_mode WAL rather than TRUNCATE or MEMORY? WAL makes the file
>     less easily moveable and introduces the shm requirement, which feels weird
>     in an Emacs context. Since Emacs itself often works along the lines of 
> "write
>     new file and replace" or "copy to backup and then overwrite", DELETE is
>     natural, but TRUNCATE feels slightly better for managed data stores. 
> MEMORY
>     would expose the DB to corruption on application crashes, but if you're
>     outside a transaction almost all the time, it might be fine... and I don't
>     see the files backend currently using backup files (or even locking) 
> anyway,
>     yes? (Or is that intended to be added later?)

MEMORY seems inappropriate, since several instances will be using the
file at the same time.  I'm not sure what the TRUNCATE would bring us.

The `files' backup doesn't lock anything, because it just wants whatever
instance manages to write the file last to do so.  But I was pondering
writing to a temp file in the same directory and doing a rename instead.

>   - Doesn't that say "bsynchronous" with an extra b? (Unrecognized pragmas are
>     normally no-ops.) Also, if you're using synchronous NORMAL rather than OFF
>     for SQLite, then presumably make sure write-region-inhibit-fsync is nil 
> while
>     comparing...

Fixed the typo.  I didn't see any noticeable difference between NORMAL
and OFF on this laptop, so I left it at NORMAL.

>   - Having auto_vacuum be FULL similarly seems like a waste, adding a bunch of
>     extra work to each transaction commit.

Likewise, I didn't see any difference between FULL and INCREMENTAL here,
and FULL means less work, so I left it at FULL.

>   - Given that people have expressed concerns over in-place modification 
> leaving
>     unwanted traces of old data, setting secure_delete to ON may be useful 
> here.

I thought the vacuum was supposed to take care of most of that?

>   - Setting trusted_schema to OFF is recommended for new applications.

I'm not sure that's relevant for this usag?

>   - You might consider using application_id to make the file identifiable as a
>     specific format rather than probing for the existence of the main table.

Sure.

>   - Doesn't the current code in multisession.el not execute the pragmas on 
> subsequent
>     opens? Only some of these are persistent.

Good point.  I've now moved the pragmas.  (But that didn't seem to
affect performance, either.)

Thanks for the tips.

-- 
(domestic pets only, the antidote for overdose, milk.)
   bloggy blog: http://lars.ingebrigtsen.no