[Bucardo-general] bucardo fails to replicate when the column name contains german umlauts

[Christian Ritter]

On 2013-09-02 11:49, Michelle Sullivan wrote:
> Christian Ritter wrote:
>> I have switched the query log on, and it looks like bucardo has
>> problems with reading the data from the source database.
>>
>>
>> my insert into database testa:
>> 2013-09-02 11:08:28 CEST LOG:  statement: INSERT INTO testtable 
>> VALUES
>> (1, '192.168.1.1', 'foobar');
>>
>> what bucardo tries to insert into database testb:
>> 2013-09-02 11:08:29 CEST LOG:  execute dbdpg_p14740_6: INSERT INTO
>> public.testtable (id, host_ip,"prüfsumme") VALUES ($1,$2,$3)
>> 2013-09-02 11:08:29 CEST DETAIL:  parameters: $1 = '1', $2 =
>> '192.168.1.1', $3 = NULL
>> 2013-09-02 11:08:29 CEST ERROR:  null value in column "prüfsumme"
>> violates not-null constraint
>> 2013-09-02 11:08:29 CEST STATEMENT:  INSERT INTO public.testtable 
>> (id,
>> host_ip,"prüfsumme") VALUES ($1,$2,$3)
>>
>> prüfsumme is quoted with "" which looks ok.
>>
>> client encoding is set to utf8:
>>
>> testa=# SHOW client_encoding;
>>  client_encoding
>> -----------------
>>  UTF8
>> (1 row)
>>
>
>
> Now that's more like it..  I'm betting the issue therefore is in the
> bucardo_delta function(s) that insert the data into the table.
>
> On the source machine try the following (assuming 4.5.x - I don't 
> know
> on 4.99.7 except this won't work):
>
> SELECT * FROM bucardo.bucardo_delta WHERE rowid = '1'::text AND 
> rowid2 =
> '192.168.1.1'::text;
>
> this will give you the rows in the bucardo_delta table and you should 
> be
> able to see if the source data is actually NULL.
>
> If it is and there are no other rows the problem will likely be in 
> the
> delta() functions.  If not the the read of the data is incorrect.  If
> there are multiple rows and one is null (earliest txntime) then it 
> may
> be because you changed the column/table definition after a delta was
> created and not replicated - in which case you will need to remove 
> the
> rows with something like:
>
> DELETE FROM bucardo.bucardo_delta WHERE rowid = '1'::text AND rowid2 
> =
> '192.168.1.1'::text AND rowid3 IS NULL; (if there are other tables 
> you
> will need to specify the table OID in the DELETE as well.  Table OIDs
> can be obtained on the source with something like:
>
> =# select relname, oid from pg_class where relname = 'hosts2evid';
>   relname   |  oid
> ------------+-------
>  hosts2evid | 32596
> (1 row)
>
> Regards,
>
> Michelle


bucardo delta looks good to me, so it looks like bucardo is unable to 
read the data if there are umlauts in the column name.

testa=# SELECT * from bucardo_delta;
  tablename | rowid |            txntime
-----------+-------+-------------------------------
      28039 | 1     | 2013-09-02 13:23:12.344017+02
(1 row)

testa=# select relname, oid from pg_class where relname = 'testtable';
   relname  |  oid
-----------+-------
  testtable | 28039


testa=# SELECT * from testtable;
  id |   host_ip   | prüfsumme
----+-------------+-----------
   1 | 192.168.1.1 | foobar
(1 row)


Cheers

Christian