Re: [exim] pgsql "client encoding" issue

Mark Adams wrote:
> Hi All,
>
> exim 4.72-6, Debian squeeze.
>
> I'm having a client encoding issue whilst trying to log headers in to
> postgres using pgsql if the subject contains the pound sign (£)
>
> I fixed other encoding issues I had by adding :rfc2047: in as follows
>
> ${quote_pgsql:rfc2047:$h_subject:}
>
> However this doesn't seem to work for the pound sign.
>
> Whats the best way to sort out encoding issues with postgres so anything
> that comes through exim can be inserted in to postgres?
>
> An example error is as follows (apologies for the wrapping):
>
> 2011-04-05 21:40:13 1Q7D2r-0001IE-6p H=fw.dabs.com [194.70.94.141]
> Warning: ACL "warn" statement skipped: condition test deferred: PGSQL:
> query failed: ERROR: invalid byte sequence for encoding "UTF8": 0xa3
> HINT: This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding". (PGRES_FATAL_ERROR) (INSERT INTO log (t_message_id,
> t_message_date, t_subject, t_recipients, t_from, t_to, t_cc, n_size)
> VALUES ('1Q7D2r-0001IE-6p', '5 Apr 2011 20:40:07 -0000', 'Fantastic
> Deals at dabs.com! £120 off Sony VAIO E Series Laptop!',
> 'itdept@???', '\"dabs.com\"<offers@???>',
> 'itdept@???', '', '63324'))
>
> Regards,
> Mark
>

Not really an Exim issue.

But '8 bit safe' dpoesn't mean '8-bit omniscient'.

Having PostgreSQL expect unicode is only half the equation.

- For DB-content you *generate* with log_message or log_write it isn't
hard to match what is wanted

- for DB content you accept 'as received' from the Wide World, where
there are dozens of non-Unicode encodings, (~ 20 for Chinese alone...)
you must either;

-- block such input (not recommended!)

-- convert it (man inconv), Quite difficult if you aren't sure what code
is in use.

-- simply insert the could-be-anything data into Postgres as a binary
object. Eg: 'don't care' what the encoding is.

Much as is done with logs written to the fs, BTW...

;-)

Note that whether they make sense to the Mark One eyeball depends on
what you choose to display the with. Storage is just binary values.


HTH,

Bill