On Feb 22, 2021, at 4:56 PM, Ron Garret (gmail) <ron...@flownet.com> wrote:
> > On Feb 22, 2021, at 2:57 PM, Wietse Venema <wie...@porcupine.org> wrote: > >> Ron Garret: >> [ Charset windows-1252 converted... ] >>> I ran into the sqlite locked database problem discussed in these threads: >>> >>> https://marc.info/?l=postfix-users&m=160096626120296&w=2 >>> >>> https://marc.info/?l=postfix-users&m=151561295721906&w=2 >>> >>> The problem occurs (AFAICT) because the database file was shared with a >>> spam filter which was writing to the db. But that raises the following >>> question: what is the right way to update a sqlite db used by postfix? The >>> only safe way I can think of doing it is to actually shut down postifx, >>> update the db, and then start postfix back up again. But that feels like >>> an overly brutal solution. Is there a better way? Even a non-shared db >>> needs to be updated now and then. >>> >>> I can guarantee that all writes will complete within a short time, so what >>> I would really like to do is to get postfix to issue a ?PRAGMA busy_timeout >>> = ?? command before doing the query, but I don?t want to have to rebuild >>> postfix from source in order to do this. Is this possible? How? >>> >> >> Isn't SQLite supposed to deal with concurrent access? >> https://sqlite.org/lockingv3.html > > Yes, it does, but the way it “deals” with it is to throw an error if one > connection tried to read while another is writing. The net result of this is > that if Postfix tries to read during a concurrent update from somewhere else, > it fails catastrophically (mail is actually lost). Just for the record: I spent some more time groveling around in the docs and source code and AFAICT it is actually not possible to safely update a sqlite DB that is in use by postfix. The only safe way to do it is to make a copy of the DB, update that, and then mv it to the active path. This is according to both the docs and the code. It would be nice if postfix would set a non-zero busy timeout. It’s a simple code change, just a call to sqlite3_busy_timeout. That would not be a guarantee, but it would at least make it *possible* to safely update a sqliite database in-place. I’m going to head on over to the postfix-dev list to see if it’s possible to get this done. rg
