From: Matthew Schumacher <matt.s@aptalaska.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Mon, 01 Aug 2005 12:04:19 -0800
Message-ID: <42EE8043.1050207@aptalaska.net> (raw)
In-Reply-To: <27897.1122748133@sss.pgh.pa.us>
References: <42E80C28.2040608@aptalaska.net>
<1122532023.25574.1.camel@localhost.localdomain>
<42E9749F.6000709@aptalaska.net>
<1122616211.5691.340.camel@lamb.mcmillan.net.nz>
<42EA6966.8090402@aptalaska.net>
<1122668371.5691.352.camel@lamb.mcmillan.net.nz>
<19405.1122670917@sss.pgh.pa.us>
<42EAA410.9010700@aptalaska.net>
<27897.1122748133@sss.pgh.pa.us>
Tom Lane wrote:
>
> Revised insertion procedure:
>
>
> CREATE or replace FUNCTION put_tokens (_id INTEGER,
> _tokens BYTEA[],
> _spam_count INTEGER,
> _ham_count INTEGER,
> _atime INTEGER)
> RETURNS VOID AS
> $$
> declare _token bytea;
> new_tokens integer := 0;
> BEGIN
> for i in array_lower(_tokens,1) .. array_upper(_tokens,1)
> LOOP
> _token := _tokens[i];
> UPDATE bayes_token
> SET spam_count = spam_count + _spam_count,
> ham_count = ham_count + _ham_count,
> atime = _atime
> WHERE id = _id
> AND token = _token;
>
> IF not found THEN
> INSERT INTO bayes_token VALUES (_id, _token, _spam_count,
> _ham_count, _atime);
> new_tokens := new_tokens + 1;
> END IF;
> END LOOP;
> if new_tokens > 0 THEN
> UPDATE bayes_vars SET token_count = token_count + new_tokens
> WHERE id = _id;
> IF NOT FOUND THEN
> RAISE EXCEPTION 'unable to update token_count in bayes_vars';
> END IF;
> END IF;
> RETURN;
> END;
> $$
> LANGUAGE plpgsql;
>
Tom, thanks for all your help on this, I think we are fairly close to
having this done in a proc. The biggest problem we are running into now
is that the data gets inserted as an int. Even though your proc defines
_token as byeta, I get numbers in the table. For example:
select put_tokens2(1, '{"\\246\\323\\061\\332\\277"}', 1, 1, 10000);
produces this:
id | token | spam_count | ham_count | atime
----+-----------------+------------+-----------+-------
1 | 246323061332277 | 1 | 1 | 10000
I'm not sure why this is happening, perhaps the problem is obvious to you?
Thanks,
schu
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: matt.s@aptalaska.net, tgl@sss.pgh.pa.us
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <42EE8043.1050207@aptalaska.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox