Steve Singer wrote:
> jks at selectacast.net wrote:
>> Slony replication between my two servers is failing with this:
>>
>> " ERROR:  invalid byte sequence for encoding "UTF8": 0xac
>> 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".
> 
> Can you determine where the value '0xac' comes from?
> 
>  CREATE TABLE a(a text);
>  INSERT INTO a VALUES ('\xac');
> 
> on a UTF8 database fails.  Does the data on your master actually contain 
> this in a text field or is the data being written into sl_log being 
> corrupted?

I don't think the data in sl_log is bad.  I did this (on both log 
tables) to find bad values and didn't find anything:

select * from _devel.sl_log_2 where log_cmddata::bytea::text like 
E'%\\012\\014%'
OR log_cmddata::bytea::text like E'%\\013\\000%'
OR log_cmddata::bytea::text like E'%\\011\\010%' ;

Interestingly enough when I tried the same thing on my data table I got 
"ERROR:  invalid input syntax for type bytea".  Checking row by row 6 
rows had this problem, but visually looking at them doesn't show 
anything obviously wrong.

> 
> 
> 
> 
>>
>> Searching the archives has revealed that people have had this problem 
>> before, but the consensus was that the problem was caused by the two 
>> pg installations having different versions or different encodings.  
>> Both my servers are pg 8.4.3, encodings are UTF8, and slony is 2.0.3.
>>
>> So my questions are:
>>
>> 1) How can I fix this problem and make sure it never happens again?
>>
>> 2) In the short run how can I break slony out of its loop?  Can I find 
>> this entry in sl_log_X somewhere and delete it?
> 
> 
> If you can identify which row in sl_log_X contains the offending line 
> you could change the log_cmddata (with an UPDATE statement) to not have 

I'm not sure how to do that. I think the multiple updates are done in 
one statement and I'm not sure how to figure out which one slony is 
trying when it gets the error.