On Feb 23, 2021, at 10:19 AM, Wietse Venema <wie...@porcupine.org> wrote:
> Ron Garret: >>>> 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 > > Bleh, it does not retry the operation? Nope. See postfix-3.5.9/src/global/dict_sqlite.c. It’s not clear that retrying would even be the right thing to do because you could just get unlucky again. The failure can happen in three different places: the query itself (obviously) but also statement preparation and finalization. I’ve seen all three actually happen in practice. So you really want it to wait. That’s a lot simpler, and it guarantees success as long as there are no slow writers (which is a reasonable constraint). > What happens when you update the table while some Postfix code is > READING from the DB? Does the writer also fail? No idea, but because I control all the writers that would be easy for me to fix. In any event I don’t think that’s something postfix should be worried about. >> result of this is that if Postfix tries to read during a concurrent >> update from somewhere else, it fails catastrophically (mail is >> actually lost). > > Losing mail would be a bug in the sending program. Postfix never > loses mail because of a fatal error. What can I say? When this happens, I can’t find the message that was being processed anywhere. It is not delivered (obviously) and it is not bounced. The way I first found out this was happening was an error notification in the root mailbox of the machine where postfix is running. >> 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. > > What about https://www.sqlite.org/pragma.html#pragma_busy_timeout ? > I don't know if that is a DB property or a session property. It’s a session/connection property. The problem with trying to use a pragma in the config file is that the C interface to sqlite does not allow multiple semicolon-separated statements in a call to sqlite3_prepare_v2, so just putting the pragma in the postfix sql config as part of the query with a semicolon after will not work. Postfix would have to know to separate multiple statements and prepare them separately. Since a source code change would be needed anyway, a much simpler solution is just to call sqlite3_busy_timeout directly. >> 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. > > If we take this route, then there needs to be a new field in the > Postfix sqlite config file that controls the time limit. Not necessarily. You could just hard-code a reasonable value (like 1 second), or make it a #define so you need a recompile to change it. That’s sub-optimal, obviously, but still a major improvement over the current situation for very little effort and no down-side that I can see. rg
