Re: Postgres problem

Hi!

I made this sql code:

ALTER TABLE bayes_token ADD COLUMN token_b char(5) NOT NULL
DEFAULT '';
UPDATE bayes_token SET token_b = (replace(encode(token,
'escape'),
'\\', '\'));
ALTER TABLE bayes_token DROP CONSTRAINT bayes_token_pkey;
ALTER TABLE bayes_token DROP COLUMN token;
ALTER TABLE bayes_token RENAME COLUMN token_b TO token;
ALTER TABLE bayes_token ADD CONSTRAINT bayes_token_pkey
PRIMARY KEY(id,
token);

It works, the only problem is, that when it tryes to create
the primary
key it cant do it, because there are not only unique fields
exists:

ERROR: could not create unique index
SQL state: 23505
Detail: Table contains duplicated values.


So, should i empty my bayesian table and start a new one?
:(

tsabi




Tóth Csaba írta:
> Hi!
> 
> I still have this problem, but i looked deeper a bit.
> 
> Maybe u remember about a thread "[Maia-users]
Postgres database
> conversion error from version 8 -> 9", the
update dont worked, and u
> suggested to convert the varchar(5) type to bytea.
> Now this is the problem, because the rpad(bytea,
integer, "unknown")
> method doesnt exists inside postgres, just the
rpad(text, int [, text]).
> And so they are not compatible.
> 
> In the thread above the solution was this:
> 
> ALTER TABLE bayes_token ADD COLUMN token_b BYTEA NOT
NULL DEFAULT '';
> UPDATE bayes_token SET token_b = (decode(replace(token,
'\', '\\'),
> 'escape'));
> ALTER TABLE bayes_token DROP CONSTRAINT
bayes_token_pkey;
> ALTER TABLE bayes_token DROP COLUMN token;
> ALTER TABLE bayes_token RENAME COLUMN token_b TO
token;
> ALTER TABLE bayes_token ADD CONSTRAINT bayes_token_pkey
PRIMARY KEY(id,
> token);
> 
> 
> Can we convert back the token field from bytea to
varchar(5)?
> 
> From the original bayes-pg.sql:
> 
> CREATE TABLE bayes_token (
>   id integer NOT NULL default '0',
>   token char(5) NOT NULL default '',
>   spam_count integer NOT NULL default '0',
>   ham_count integer NOT NULL default '0',
>   atime integer NOT NULL default '0',
>   PRIMARY KEY  (id,token)
> );
> 
> 
> 
> ty for helping,
> tsabi
> 
> 
> 
> David Morton írta:
>> On Feb 5, 2007, at 9:45 AM, Tóth Csaba wrote:
>>
>>> Hi!
>>> I have a serious problem with Maia 1.0.2 with
PostgreSQL 8.2. I haven't
>>> got this problem before the upgrade (Maia 1.0.1
and PostgreSQL 8.1).
>> What version of Spamassassin?
>>
>> I think we need to file a bug report with the
spamassassin folks.
>>
>> David Morton
>> Maia Mailguard http://www.maiamailguard
.com
>> mortondadgrmm.net
>>
>>
>>
> 
> 
>
------------------------------------------------------------
------------
> 
> _______________________________________________
> Maia-users mailing list
> Maia-usersrenaissoft.com
> http://www.renaissoft.com/mailman/listinfo/maia-users



_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

Hi!

Ok, it solved at last.

After i changed the storage module to PgSQL from normal SQL,
there were
just some inner procedures missed, what is well explained
here:
http://w
ww.maiamailguard.com/maia/ticket/420 ...

after i created the procedures everything works fine again.

So, in a nutshell, the normal SQL bayesian storage module
uses a char(5)
field to store the bayesian ID, but the PgSQL storage driver
uses a
bytea type field. So everything was ok with my database,
just should
select the proper storage driver. The missing inner
procedures were
about that maia's db schema was not so up to date. But this
is my fault
too, as i should always check what is updated exactly in a
program, when
i update it to a newer version.

best regards,
tsabi


Tóth Csaba írta:
> Hi!
> 
> I made this sql code:
> 
> ALTER TABLE bayes_token ADD COLUMN token_b char(5) NOT
NULL DEFAULT '';
> UPDATE bayes_token SET token_b = (replace(encode(token,
'escape'),
> '\\', '\'));
> ALTER TABLE bayes_token DROP CONSTRAINT
bayes_token_pkey;
> ALTER TABLE bayes_token DROP COLUMN token;
> ALTER TABLE bayes_token RENAME COLUMN token_b TO
token;
> ALTER TABLE bayes_token ADD CONSTRAINT bayes_token_pkey
PRIMARY KEY(id,
> token);
> 
> It works, the only problem is, that when it tryes to
create the primary
> key it cant do it, because there are not only unique
fields exists:
> 
> ERROR: could not create unique index
> SQL state: 23505
> Detail: Table contains duplicated values.
> 
> 
> So, should i empty my bayesian table and start a new
one? :(
> 
> tsabi
> 
> 
> 
> 
> Tóth Csaba írta:
>> Hi!
>>
>> I still have this problem, but i looked deeper a
bit.
>>
>> Maybe u remember about a thread "[Maia-users]
Postgres database
>> conversion error from version 8 -> 9", the
update dont worked, and u
>> suggested to convert the varchar(5) type to bytea.
>> Now this is the problem, because the rpad(bytea,
integer, "unknown")
>> method doesnt exists inside postgres, just the
rpad(text, int [, text]).
>> And so they are not compatible.
>>
>> In the thread above the solution was this:
>>
>> ALTER TABLE bayes_token ADD COLUMN token_b BYTEA
NOT NULL DEFAULT '';
>> UPDATE bayes_token SET token_b =
(decode(replace(token, '\', '\\'),
>> 'escape'));
>> ALTER TABLE bayes_token DROP CONSTRAINT
bayes_token_pkey;
>> ALTER TABLE bayes_token DROP COLUMN token;
>> ALTER TABLE bayes_token RENAME COLUMN token_b TO
token;
>> ALTER TABLE bayes_token ADD CONSTRAINT
bayes_token_pkey PRIMARY KEY(id,
>> token);
>>
>>
>> Can we convert back the token field from bytea to
varchar(5)?
>>
>> From the original bayes-pg.sql:
>>
>> CREATE TABLE bayes_token (
>>   id integer NOT NULL default '0',
>>   token char(5) NOT NULL default '',
>>   spam_count integer NOT NULL default '0',
>>   ham_count integer NOT NULL default '0',
>>   atime integer NOT NULL default '0',
>>   PRIMARY KEY  (id,token)
>> );
>>
>>
>>
>> ty for helping,
>> tsabi
>>
>>
>>
>> David Morton írta:
>>> On Feb 5, 2007, at 9:45 AM, Tóth Csaba wrote:
>>>
>>>> Hi!
>>>> I have a serious problem with Maia 1.0.2
with PostgreSQL 8.2. I haven't
>>>> got this problem before the upgrade (Maia
1.0.1 and PostgreSQL 8.1).
>>> What version of Spamassassin?
>>>
>>> I think we need to file a bug report with the
spamassassin folks.
>>>
>>> David Morton
>>> Maia Mailguard http://www.maiamailguard
.com
>>> mortondadgrmm.net
>>>
>>>
>>>
>>
>>
------------------------------------------------------------
------------
>>
>> _______________________________________________
>> Maia-users mailing list
>> Maia-usersrenaissoft.com
>> http://www.renaissoft.com/mailman/listinfo/maia-users
> 
> 
> 
>
------------------------------------------------------------
------------
> 
> _______________________________________________
> Maia-users mailing list
> Maia-usersrenaissoft.com
> http://www.renaissoft.com/mailman/listinfo/maia-users



_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users