Re: [Sqlgrey-users] 1.7.4 warnings

[Lionel B. <lio...@bo...>]

Dan Faerch wrote the following on 20.01.2007 22:53 :
>
> Not a bad idea at all.. Except for the race condition you mention and 
> having to change the db-schema, which you explicitly told me not to do ;)
>
>   
On 1.6.x yes. Database schema changes are tricky, because you don't want
to have to revert them so you better get them right in the first place.
This said 1.7.x is the place where you can have database schema changes.

>
>   
>>> You could have race conditions where 2+ SQLgrey wants to update at the
>>> very same time. 
>>>     
>>>       
> For problems like this, i usually do random(0-x) sleeping , before 
> UPDATE'ing the timestamp.. It decreases the likelihood of a collision 
> tremendously.
> Like this:
> SELECT.
>   

I don't really understand what you meant with this SELECT.

> Determine if its time to do clean up..
> If yes -> sleep random(0-x)
> UPDATE (blahblah) WHERE `timestamp`='<timestamp from SELECT>'
> If affected_rows > 0 then do_cleanup.
>
>   

Hum I forgot about this method. But why sleep? Assuming you have the
expected timestamp at which you think you should cleanup (the cached
value I spoke of) :
UPDATE <table> SET timestamp = expected where timestamp < expected.

And as you pointed out, the number of affected rows tells us if we are
responsible for the cleanup:
- 0: no someone else managed to modify the timestamp just before us
(just update the "expected" cache)
- 1: yes we are the one ! update the "expected" cache and cleanup.

No need to sleep, no two clients can really update the same row given
that they all have the same "expected" value (and in the buggy case
where they aren't properly configured, they won't try to update at the
same time anyway). Robust, clean, ensures only one cleanup so minimum
load, doesn't even need transactions so should work with all DBs.

> I dont suppose, due to sqlgrey's multithreaded nature, that this will 
> cause it to hang for the sleep time?
>
>   

SQLgrey is not multithreaded. It's a single process that multiplexes the
incoming Postfix requests into one sequential flow of queries. Given
that querying a local database is fast, there's no need for multiple
processes or threads (which probably better explains my comments on DNS
queries).

>> - SPF checking would be really cool :). Anyone who knows which spf-lib for perl is the "standard"?
>>     
> ....
>   
>> Hum, you do what you want with 1.7.x, but I'm considering SPF as a dying horse begging for someone to put an end to its misery... 
>>     
> I am SO hoping SPF will be more common (since it is, in theory, a great 
> idea). And adding to sqlgrey, so SPF=whitelist, might help the 
> adaptation. :) But if me (and the Dmitry122, who posted the idea), are 
> the only ones who likes it, i really wont bother :)
>   

SPF breaks email forwarding (SRS is a solution but _all_ forwarding
mailservers should support it in order for it to work properly), only
tells you that the server is authorized for a domain and not if it wants
to send SPAM and is so poorly understood that it breaks email in many
places (I've seen anti-SPAM appliances/proprietary software which
checked the From header instead of the Return-path for example...).

Lionel.