I am using the database schema delivered with SA which for the awl table is:
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
email varchar(255) NOT NULL default '',
ip varchar(40) NOT NULL default '',
count bigint NOT NULL default '0',
totscore float NOT NULL default '0',
signedby varchar(255) NOT NULL default '',
PRIMARY KEY (username,email,signedby,ip)
);
and you are right indeed, the primary key is based on the username, email,
signedby and ip columns.
So if I understand correctly this behavior is expected/normal and not an error
from my side (for example having done something wrong in the config file or so).
On Wednesday, July 30, 2014 3:02 PM, Kevin A. McGrail <kmcgr...@pccc.com> wrote:
On 7/30/2014 6:28 AM, ML mail wrote:
Hi,
>
>I am using a PostgreSQL database to store the AutoWhiteList
entries but from time to time I see in my PostgreSQL log file
the following entries:
>
>2014-07-30 12:07:42 CEST ERROR: duplicate key value violates
unique constraint "awl_pkey"
>2014-07-30 12:07:42 CEST DETAIL: Key (username, email,
signedby, ip)=(amavis, em...@domain.tld, , none) already exists.
>2014-07-30 12:07:42 CEST STATEMENT: INSERT INTO awl
(username,email,ip,count,totscore) VALUES ($1,$2,$3,$4,$5)
>
>
>It looks like SpamAssassin tries to INSERT an entry (e-mail address) which
>already exists. Shouldn't SpamAssassin AWL code first check if an entry exists
>or not and use UPDATE instead of INSERT if an entry already exists?
>
>
>Regards
>ML
>
What's your database layout? SA's pg awl layout using a primary key on all 4
items PRIMARY KEY (username,email,signedby,ip). However, I think that your
example that it's just email address is not a cut and paste.
So beyond that, based on add_score, the behavior you are seeing is
expected:
# try inserting first, and if that fails we'll do the update; this
way
# we avoid to large extent a race condition between multiple
processes
Not saying there isn't a better way to do it but it does seem to be
on purpose.
Regards,
KAM
