postfix-users October 2010 archive
Main Archive Page > Month Archives  > postfix-users archives
postfix-users: Re: verify db with mysql

Re: verify db with mysql

From: Stefan Jakobs <stefan_at_nospam>
Date: Thu Oct 28 2010 - 20:45:20 GMT
To: postfix-users@postfix.org

On Friday 15 October 2010 16:53:40 Victor Duchovni wrote:
> On Fri, Oct 15, 2010 at 03:05:33PM +0200, Stefan wrote:
> > in the appendix you will find a patch against Postfix 2.7.1 which adds
> > write support to Postfix' MySQL client.
> >
> > If someone like to test it, then he will find Postfix RPMs with MySQL
> > write support for recent versions of *SUSE linux here:
> > http://download.opensuse.org/repositories/home:/rusjako/
> >
> > To use a MySQL verify db, you have to:
> > - add "address_verify_map = mysql:/etc/postfix/verify.cf" to your main.cf
> > - Content of /etc/postfix/verify.cf:
> > user = postfix
> > password = <secret>
> > dbname = postfix
> > query = SELECT data FROM verify WHERE address='%s'
> > delete = DELETE FROM verify WHERE address='%s'
> > insert = INSERT verify SET address='%s', data='%v'
> > update = UPDATE verify SET data='%v' WHERE address='%s'
> > sequence = SELECT address,data FROM verify
>
> Have you found any issues with lock contention between the
> "sequence" pseudo-thread and INSERT/DELETE/UPDATE operations during a
> garbage-collection sweep?

No.

> Is the code known to be safe against dead-lock?

I'am not aware of any dead-lock issues. The sequence pseudo-thread will query
the database only once with the first key. For every next key the sequence
pseudo-thread is working with the results in the memory. With a very large
database the size of the response may be a problem. But a INSERT/DELETE/UPDATE
operation will not conflict with the sequence pseudo-thread.
Finally, I can not prove if the code is dead-lock safe.

> Also it probably makes sense to retain a compatible db_common_expand()
> wrapper around the extended code that also handles a second "value"
> element in addition to lookup the key. This would obviate the need
> to modify the other table drivers that don't do updates...

Yes, good idea. I fixed that in the appended patch.

Best regards
Stefan