Comment by porridgeraisin
Comment by porridgeraisin 2 days ago
You should pragna optimize before TX end, not at tx start.
Except for long lived connections where you do it periodically.
https://www.sqlite.org/lang_analyze.html#periodically_run_pr...
Comment by porridgeraisin 2 days ago
You should pragna optimize before TX end, not at tx start.
Except for long lived connections where you do it periodically.
https://www.sqlite.org/lang_analyze.html#periodically_run_pr...
Yes, if journal_mode was not sticky, a new process opening the db would not know to look for the wal and shm files and read the unflushed latest data from there. On the other hand, foreign key enforcement has nothing to do with the file itself, it's a transaction level thing.
In any case, there is no harm in setting sticky pragmas every connection.
Also foreign_keys has to be set per connection but journal_mode is sticky (it changes the database itself).