Re: sqlite3 usage for multisession variable storage

[Robin Tarsiger]

Lars Ingebrigtsen wrote:
> 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.

MEMORY doesn't disable the use of exclusive file locking during write
transactions. Several concurrent instances successfully committing or
rolling back in MEMORY mode will have the same consistency constraints.
The difference is in crash recovery; if one instance crashes hard in the
middle of a transaction, it's "restore the file from backup" time.
Whether that's okay depends on your surrounding strategy, but it feels
similar to a subset of the lockfile strategies Emacs already considers
okay.

TRUNCATE is just slightly less operations than DELETE in the case where
you do want to have a full rollback journal and don't care about the
directory looking tidy.

Hmm. What pattern/frequency/relative-frequency of reads/writes is expected
to be common for these variables? And in particular, what about read-
modify-write operations? Will applications tend to expect these to be
atomic (deliberately or otherwise)?

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

Yeah, overwrites are still non-atomic in the easiest case. :-)

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

Auto-vacuuming only makes sure free pages are deallocated so that the
database file shrinks as data is removed, even in FULL mode. It does not
necessarily overwrite stale data with zeros, and as mentioned in the
documentation, it can actually make fragmentation worse by being too
aggressive with moving pages around. Normally I would leave it off.

If you want periodic maintenance of the DB file, there may also be
application-level maintenance that would be useful, as I extrapolate it?
Depending on what's stored in these variables, clearing out stale entries
and the like may want to be done at an elisp level. Leaving cleanup+VACUUM
to be done explicitly on timer or user request and then having a hook for
the non-DB-format-related cleanup might be workable? Not sure.

> >    - Setting trusted_schema to OFF is recommended for new applications.
>
> I'm not sure that's relevant for this usag?

It cheaply removes a little chunk of potentially surprising behaviors. But
you may be right in a sense---it depends on whether elisp assumes it can
trust values read from the store. If so, then the entire DB is just in the
same "may do anything" trusted zone as the rest of .emacs.d, I suppose?

-RTT