What is the right way to update a postfix sqlite database?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

What is the right way to update a postfix sqlite database?

Ron Garret-2
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?

Thanks,
rg

Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Wietse Venema
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

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Ron Garret-2
On Feb 22, 2021, at 4:56 PM, Ron Garret (gmail) <[hidden email]> wrote:

>
> On Feb 22, 2021, at 2:57 PM, Wietse Venema <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

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

What happens when you update the table while some Postfix code is
READING from the DB? Does the writer also fail?

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

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

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

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Viktor Dukhovni
On Tue, Feb 23, 2021 at 01:19:24PM -0500, Wietse Venema wrote:

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

Only if you specify a retry timeout. SQLite is mostly for embedded
use-cases, and support for sharing has warts.

> What happens when you update the table while some Postfix code is
> READING from the DB? Does the writer also fail?

No, only if the writer has no retry timeout.  This typically works,
but is sub-optimal.

> > 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 is a session property.

> If we take this route, then there needs to be a new field in the
> Postfix sqlite config file that controls the time limit.

Yes.

--
    Viktor.
Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Ron Garret-2
In reply to this post by Wietse Venema

On Feb 23, 2021, at 10:19 AM, Wietse Venema <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Wietse Venema
Ron Garret:
> > 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.

The limit should be configurable. It takes:

- one line of code to define a C variable,

- one line of code to read its value from an sqlite_table configuration
  file (or to use a documented default value),

- a few lines of text to document the new field in the sqlite_table manpage.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Richard Damon
On 2/23/21 2:18 PM, Wietse Venema wrote:

> Ron Garret:
>>> 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.
> The limit should be configurable. It takes:
>
> - one line of code to define a C variable,
>
> - one line of code to read its value from an sqlite_table configuration
>   file (or to use a documented default value),
>
> - a few lines of text to document the new field in the sqlite_table manpage.
>
> Wietse

One thng to look at is WAL mode. WAL mode increases the cost of writes
to the database, as all writes become two stage, first to the WAL
journal, and then flushed to the main database (called A checkpoint),
and reads reads can get a bit more expensive if the second stage of the
write gets delayed by long accesses (but that may not be an issue with
postfix).

In exchange, the database allows for simultaneous reads and writes,
except possibly for the period when the second phase of the writes are
occurring, and it will try to allow as much overlap there as possible,
and try to find a time when no readers are active to do this operation.

Without a busy timeout being set, the reader should only get a busy in
fairly rare conditions, the main one being if the last connection to the
database is closing, then SQLite does some cleanup that locks the
database for just a little bit, or if the last connection 'crashes' than
the next connection will do some cleanup. Even a fairly short busy wait
should handle these cases most of the time.

--
Richard Damon

Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Ron Garret-2

On Feb 23, 2021, at 11:41 AM, Richard Damon <[hidden email]> wrote:

> On 2/23/21 2:18 PM, Wietse Venema wrote:
>> Ron Garret:
>>>> 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.
>> The limit should be configurable. It takes:
>>
>> - one line of code to define a C variable,
>>
>> - one line of code to read its value from an sqlite_table configuration
>>  file (or to use a documented default value),
>>
>> - a few lines of text to document the new field in the sqlite_table manpage.
>>
>> Wietse
>
> One thng to look at is WAL mode. WAL mode increases the cost of writes
> to the database, as all writes become two stage, first to the WAL
> journal, and then flushed to the main database (called A checkpoint),
> and reads reads can get a bit more expensive if the second stage of the
> write gets delayed by long accesses (but that may not be an issue with
> postfix).
>
> In exchange, the database allows for simultaneous reads and writes,
> except possibly for the period when the second phase of the writes are
> occurring, and it will try to allow as much overlap there as possible,
> and try to find a time when no readers are active to do this operation.
>
> Without a busy timeout being set, the reader should only get a busy in
> fairly rare conditions, the main one being if the last connection to the
> database is closing, then SQLite does some cleanup that locks the
> database for just a little bit, or if the last connection 'crashes' than
> the next connection will do some cleanup. Even a fairly short busy wait
> should handle these cases most of the time.

WAL mode was previously discussed here:

https://marc.info/?l=postfix-users&m=160096626120296&w=2

The upshot appears to be this, at least as things currently stand:

> DO NOT use SQLite as a Postfix backend database updated live while
> Postfix is running.

rg

Reply | Threaded
Open this post in threaded view
|

Re: What is the right way to update a postfix sqlite database?

Wietse Venema
Ron Garret:
> WAL mode was previously discussed here:
>
> https://marc.info/?l=postfix-users&m=160096626120296&w=2

In other words the reader requires database write permission. I
fully agree that is not desirable.

> The upshot appears to be this, at least as things currently stand:
>
> > DO NOT use SQLite as a Postfix backend database updated live while
> > Postfix is running.

Just like other file-based databases, the safest update
approach is

        create a temporary db file
        rename the temporary db file

The exception is LMDB which can handle concurrent reads and writes
without reader permission errors, busy errors, and so on.

So, who adds a busy timer to the sqlite client?

        Wietse