Re: amavisd-new with postgresql help

Christoph Moench-Tegeder skrev den 2023-06-14 23:05:
> ## Benny Pedersen ([email protected]):
> 
>> $sql_select_white_black_list = 'SELECT wb FROM wblist,mailaddr WHERE
>> (wblist.rid=?) AND (wblist.sid=mailaddr.id) AND (mailaddr.email IN
>> (%k)) ORDER BY mailaddr.priority DESC';
> 
>> Jun 14 21:46:02 localhost postgres[22001]: [7-1] 2023-06-14
>> 19:46:02.275 UTC [22001] ERROR:  operator does not exist: bytea =
>> character varying at character 88
> 
> So that's a datatype mismatch.
> Short answer: README_FILES/README.sql-pg and RELEASE_NOTES (ok,
> at line 6416).

can't fint that line :/

>> Jun 14 21:46:02 localhost postgres[22001]: [7-2] 2023-06-14
>> 19:46:02.275 UTC [22001] HINT:  No operator matches the given name and
>> argument types. You might need to add explicit type casts.
>> Jun 14 21:46:02 localhost postgres[22001]: [7-3] 2023-06-14
>> 19:46:02.275 UTC [22001] STATEMENT:  SELECT *,users.id FROM
>> users,policy WHERE (users.policy_id=policy.id) AND (users.email IN
>> ($1,$2,$3,$4,$5,$6)) ORDER BY users.priority DESC
> 
> And there's the statement for that - it's $sql_select_white_black_list.
> Counting characters, it's the "users.email IN (...)" part.
> And given that bytea is not a preferred type, that has to come from
> somewhere, and from the order of operands I believe it's the query, so
> I think you set "$sql_allow_8bit_address = 1" (that is not the

indeed missed $sql_allow_8bit_address = 1 in my amavisd.conf, after this 
is changed all works for sql data as designed :=)

> default!) which forces the %k expansion to be passed as a bytea
> parameter, but you left the table at the "legacy" form with the
> text datatype for column email.
> 
> You should make sure that your configuration (sql_allow_8bit_address)
> matches your database.

yes thanks for help me find it was just that

i like to find now how to expire data in the sql database, will i have 
to make a cron that executes a sql file ?