You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by Ingo Freund <sp...@e-dict.net> on 2006/03/27 16:54:12 UTC
errors in postgres database queries
Hi list,
after my upgrade to spamassassin version 3.1.0
and migrate the database from mySQL to PostgreSQL there are
coming up many errors in the postgres logfile all like this:
ERROR: operator does not exist: character = bytea
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
after running spamd with -D it shows:
Mar 27 16:48:13 base spamd[2117]: spamd: checking message (unknown) for (unknown):98
Mar 27 16:48:13 base spamd[2117]: dns: name server: 127.0.0.1, family: 2, ipv6: 0
Mar 27 16:48:13 base spamd[2117]: received-header: parsed as [ ip=218.248.20.20 rdns= helo=-1208590568 by=e-dict.net ident= envfrom=
intl=0 id=k2REm3oO002642 auth= ]
Mar 27 16:48:13 base spamd[2117]: received-header: relay 218.248.20.20 trusted? no internal? no
Mar 27 16:48:13 base spamd[2117]: metadata: X-Spam-Relays-Trusted:
Mar 27 16:48:13 base spamd[2117]: metadata: X-Spam-Relays-Untrusted: [ ip=218.248.20.20 rdns= helo=-1208590568 by=e-dict.net ident=
envfrom= intl=0 id=k2REm3oO002642 auth= ]
Mar 27 16:48:13 base spamd[2117]: message: ---- MIME PARSER START ----
Mar 27 16:48:13 base spamd[2117]: message: main message type: text/plain
Mar 27 16:48:13 base spamd[2117]: message: parsing normal part
Mar 27 16:48:13 base spamd[2117]: message: added part, type: text/plain
Mar 27 16:48:13 base spamd[2117]: message: ---- MIME PARSER END ----
Mar 27 16:48:13 base spamd[2117]: message: no encoding detected
Mar 27 16:48:13 base spamd[2117]: rules: local tests only, ignoring RBL eval
Mar 27 16:48:13 base spamd[2117]: check: running tests for priority: 0
Mar 27 16:48:13 base spamd[2117]: rules: running header regexp tests; score so far=0
Mar 27 16:48:13 base spamd[2117]: rules: ran header rule __HAS_RCVD ======> got hit: "f"
Mar 27 16:48:13 base spamd[2117]: eval: all '*From' addrs:
Mar 27 16:48:13 base spamd[2117]: eval: all '*To' addrs:
Mar 27 16:48:13 base spamd[2117]: rules: ran eval rule MISSING_HB_SEP ======> got hit
Mar 27 16:48:13 base spamd[2117]: eval: forged-HELO: from= helo=-1208590568 by=e-dict.net
Mar 27 16:48:13 base spamd[2117]: rules: ran eval rule __ENV_AND_HDR_FROM_MATCH ======> got hit
Mar 27 16:48:13 base spamd[2117]: eval: trying Received header date for real time: 27 Mar 2006 16:48:09 (CEST)
Mar 27 16:48:13 base spamd[2117]: eval: time_t from date=1143470889, rcvd= 27 Mar 2006 16:48:09 (CEST)
Mar 27 16:48:13 base spamd[2117]: eval: date chosen from message: Mon Mar 27 16:48:09 2006
Mar 27 16:48:13 base spamd[2117]: rules: running body-text per-line regexp tests; score so far=2.5
Mar 27 16:48:13 base spamd[2117]: uri: running uri tests; score so far=2.5
Mar 27 16:48:13 base spamd[2117]: bayes: corpus size: nspam = 239, nham = 75492
Mar 27 16:48:13 base spamd[2117]: bayes: tok_get_all: token count: 15
Mar 27 16:48:13 base spamd[2117]: bayes: tok_get_all: SQL error: ERROR: operator does not exist: character = bytea
Mar 27 16:48:13 base spamd[2117]: bayes: HINT: No operator matches the given name and argument type(s). You may need to add xplicit
type casts.
Mar 27 16:48:13 base spamd[2117]: bayes: cannot use bayes on this message; none of the tokens were found in the database
Mar 27 16:48:13 base spamd[2117]: bayes: not scoring message, returning undef
Mar 27 16:48:13 base spamd[2117]: bayes: get_running_expire_tok: SQL error: ERROR: current transaction is aborted, commands ignored
until end of transaction block
Mar 27 16:48:13 base spamd[2117]: bayes: get_storage_variables: SQL error: ERROR: current transaction is aborted, commands ignored
until end of transaction block
Mar 27 16:48:13 base spamd[2117]: bayes: DB expiry: tokens in DB: 0, Expiry max size: 150000, Oldest atime: 0, Newest atime: 0, Last
expire: 0, Current time: 1143470893
Mar 27 16:48:13 base spamd[2117]: rules: running raw-body-text per-line regexp tests; score so far=2.5
Mar 27 16:48:13 base spamd[2117]: rules: running full-text regexp tests; score so far=2.5
Mar 27 16:48:13 base spamd[2117]: check: running tests for priority: 500
Mar 27 16:48:13 base spamd[2117]: rules: running meta tests; score so far=2.5
Mar 27 16:48:13 base spamd[2117]: rules: running header regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running body-text per-line regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: uri: running uri tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running raw-body-text per-line regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running full-text regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: check: running tests for priority: 1000
Mar 27 16:48:13 base spamd[2117]: rules: running meta tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running header regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running body-text per-line regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: uri: running uri tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running raw-body-text per-line regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: rules: running full-text regexp tests; score so far=6.392
Mar 27 16:48:13 base spamd[2117]: plugin: Mail::SpamAssassin::Plugin::AutoLearnThreshold=HASH(0x8eb44d4) implements
'autolearn_discriminator'
Mar 27 16:48:13 base spamd[2117]: learn: auto-learn: currently using scoreset 2, recomputing score based on scoreset 0
Mar 27 16:48:13 base spamd[2117]: learn: auto-learn: message score: 6.392, computed score for autolearn: 5.794
Mar 27 16:48:13 base spamd[2117]: learn: auto-learn? ham=0.1, spam=12, body-points=0, head-points=2.5, learned-points=0
Mar 27 16:48:13 base spamd[2117]: learn: auto-learn? no: inside auto-learn thresholds, not considered ham or spam
Mar 27 16:48:13 base spamd[2117]: check: is spam? score=6.392 required=5
Mar 27 16:48:13 base spamd[2117]: check: tests=EMPTY_MESSAGE,MISSING_HB_SEP,MISSING_SUBJECT,TO_CC_NONE
Mar 27 16:48:13 base spamd[2117]: check: subtests=__ENV_AND_HDR_FROM_MATCH,__HAS_RCVD
Mar 27 16:48:13 base spamd[2117]: spamd: identified spam (6.4/5.0) for (unknown):98 in 4.0 seconds, 148 bytes.
Mar 27 16:48:13 base spamd[2117]: spamd: result: Y 6 - EMPTY_MESSAGE,MISSING_HB_SEP,MISSING_SUBJECT,TO_CC_NONE
scantime=4.0,size=148,user=(unknown),uid=98,required_score=5.0,rhost=localhost,raddr=127.0.0.1,rport=56502,mid=(unknown),autolearn=no
Mar 27 16:48:13 base spamd[2117]: config: copying current conf from backup
Mar 27 16:48:13 base spamd[2117]: prefork: sysread(8) not ready, wait max 300 secs
Mar 27 16:48:13 base spamd[2113]: prefork: child 2117: entering state 1
...
What am I doing wrong, can anybody help?
-Ingo.
Re: errors in postgres database queries
Posted by Ingo Freund <sp...@e-dict.net>.
Michael Parker wrote:
> Ingo Freund wrote:
>
>>The system worked well with mySQL.
>
>
> WARNING: Bayes SQL does indeed work best with MySQL. Feel free to
> search the users/dev list in the past for more information.
>
>
That might be, but where should be the need to let two different
databases work if one could do it?
On the servers here high performance ist not the important question.
-Ingo.
PostgreSQL C functions
Posted by Christopher Bergström <cb...@netsyncro.com>.
I've been reading archives from blogs, postgres mailing lists and here.
After some testing I do seem to get the best results from the
suggestions by Tom Lane. My scheme minus the GREATEST/LEAST [1] is the
same as the suggestions.
1) I'm not certain my sa_bayes scheme is error free (Should I pull from
SVN/snapshot/tarball or is there maybe something not even filed upstream
I should look at?)
2) Based on my benchmarks alone I see positive performance gains, but
still want to ask the list for feedback. [2]
3) It's been discussed to rework the Perl module.. Please pardon me for
not just looking if there's an included TODO, but have most of the
suggestions been a) filed upstream and or b) integrated in the latest
release.
Bottom line question is this : Should I post my scheme based on Tom
Lane's suggestions, make sure that it's solid/error free or is there a
scheme with functions already that I should convert?
Most Kind Regards,
Christopher Bergström
[1] Starting at PostgreSQL 8.1.x has built-in GREATEST/LEAST iirc
[2] After each set of changes I ran the test twice (minus run 6) The
specs on this test box were very low.. AMD Athlon bogomips :
2092.08/256MB ram, and Raptor hd. (, but it's running at udma5 not 6
because I haven't patched the controller module) I've misplaced the link
to the test case, but searching on the postgres mailing lists for
Spamassassin should bring some results. (Or I can just upload.)
1st)
real 13m16.751s
user 0m20.189s
sys 0m3.900s
real 13m18.431s
user 0m20.237s
sys 0m3.912s
2nd)
real 13m11.233s
user 0m19.233s
sys 0m4.004s
real 13m13.561s
user 0m19.269s
sys 0m4.020s
3rd)
real 4m2.857s
user 0m19.513s
sys 0m3.812s
real 4m4.051s
user 0m19.549s
sys 0m3.840s
4th)
real 3m33.875s
user 0m19.773s
sys 0m3.736s
real 3m34.756s
user 0m19.821s
sys 0m3.748s
5th)
real 3m25.737s
user 0m18.709s
sys 0m4.808s
real 3m27.085s
user 0m18.761s
sys 0m4.820s
* Here is where I changed the SQL per Tom Lane's recommendations (I was
too lazy to run twice, sorry)
6th)
real 2m16.640s
user 0m17.589s
sys 0m4.644s
Re: errors in postgres database queries
Posted by Ingo Freund <sp...@e-dict.net>.
Michael Parker schrieb:
> Ingo Freund wrote:
>
>>The system worked well with mySQL.
>
>
> WARNING: Bayes SQL does indeed work best with MySQL. Feel free to
> search the users/dev list in the past for more information.
>
>
>>Yes I followed the instructions.
>>Migrating to Postgres went through without any problems.
>>
>>This is what spamd uses:
>>bayes_store_module Mail::SpamAssassin::BayesStore::PgSQL
>>
>
>
> What version of Postgres? I seem to remember folks with versions < 8
> having some issues.
>
> Michael
It is postgres 8.1.2
-Ingo.
Re: errors in postgres database queries
Posted by Michael Parker <pa...@pobox.com>.
Ingo Freund wrote:
>
> The system worked well with mySQL.
WARNING: Bayes SQL does indeed work best with MySQL. Feel free to
search the users/dev list in the past for more information.
> Yes I followed the instructions.
> Migrating to Postgres went through without any problems.
>
> This is what spamd uses:
> bayes_store_module Mail::SpamAssassin::BayesStore::PgSQL
>
What version of Postgres? I seem to remember folks with versions < 8
having some issues.
Michael
Re: errors in postgres database queries
Posted by Ingo Freund <sp...@e-dict.net>.
Michael Parker wrote:
> Ingo Freund wrote:
>
>>Hi list,
>>
>>after my upgrade to spamassassin version 3.1.0
>>and migrate the database from mySQL to PostgreSQL there are
>>coming up many errors in the postgres logfile all like this:
>>
>>ERROR: operator does not exist: character = bytea
>>HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
>>ERROR: current transaction is aborted, commands ignored until end of transaction block
>>ERROR: current transaction is aborted, commands ignored until end of transaction block
>>
>
>
> What are you using for bayes_store_module? Also, did you follow the
> instructions in sql/README.bayes for installing all the proper pieces to
> get Postgres up and running properly?
>
> Michael
The system worked well with mySQL.
Yes I followed the instructions.
Migrating to Postgres went through without any problems.
This is what spamd uses:
bayes_store_module Mail::SpamAssassin::BayesStore::PgSQL
Ingo.
Re: errors in postgres database queries
Posted by Michael Parker <pa...@pobox.com>.
Ingo Freund wrote:
> Hi list,
>
> after my upgrade to spamassassin version 3.1.0
> and migrate the database from mySQL to PostgreSQL there are
> coming up many errors in the postgres logfile all like this:
>
> ERROR: operator does not exist: character = bytea
> HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> ERROR: current transaction is aborted, commands ignored until end of transaction block
>
What are you using for bayes_store_module? Also, did you follow the
instructions in sql/README.bayes for installing all the proper pieces to
get Postgres up and running properly?
Michael