[Bucardo-general] Concurrency errors when replicating a master to two slaves and installation experiences

[Geoff Williams]

Hi Greg,

> Good to know, worth a doc patch if anyone wants to step up (or 
> just edit the wiki).
Have edited the wiki and added a new page about .pgpass files

> > 2) There seems to be an undocumented ~20 character limit to the 
> > length of a sync name.[...]
> There should be no length limit. There is some munging of the auto-generated 
> trigger and notification names [...] Are any of the syncs using uppercase 
> characters by chance?
I'm definitely still hitting a problem with long sync names, I've been
experimenting again this evening.  There are no upper case characters in
any of the database or sync names.

In my tests, I was able to get inserts, deletes and truncates to
replicate but the sync stayed stuck in onetimecopy mode and I saw
messages like this in the bucardo.warning.log:

[Mon Nov 29 19:19:58 2010]  CTL Controller exiting at
cleanup_controller. Reason: DBD::Pg::st execute failed: ERROR:  insert
or update on table "q" violates foreign key constraint "q_tdb_fk"
DETAIL:  Key (targetdb)=(jefferson_airplane_server) is not present in
table "db". at /usr/lib/perl5/vendor_perl/5.8.8/Bucardo.pm line 3233.

The proper name of the database was "jefferson_airplane_server1" but it
got truncated internally somewhere to give the error above.  When I
changed the sync name to "aaaaaaaaaa" from "aaaaaaaaaaaaaaaaaaaa" and
restarted everything I no longer got the message and the sync finished
its one time copy.

> Not sure I understand this one yet. The triggerkick functions should 
> only be doing a NOTIFY, which requires no extra perms. Can you verify 
> what those functions are doing with a \df+?
Had a look at the triggerkick - it does do a notify but if your
truncating a table then it also tries to do an insert.  This is where my
permission denied error must be coming from.  Here's the offending block
of code from one of our production databases:
 
EXECUTE 'NOTIFY "bucardo_kick_sync_ms_p_maplayers"';
IF TG_OP = 'TRUNCATE' THEN
INSERT INTO bucardo.bucardo_truncate_trigger(tablename,sname,tname,sync)
VALUES (TG_RELID, TG_TABLE_SCHEMA, TG_TABLE_NAME, 'ms_p_maplayers');
END IF;     

> There's definitely a bad race condition with the sequences table. 
> I've not back-patched it yet, but need to. Keep reminding me. :)
I'm hitting this bug in production every few days, replication gets
'stuck' and I have to reload the sync to unblock things.  By way of
background, we have a bunch of scripts that do a truncate followed by
hundreds of inserts/updates every day.  Putting all the operations
inside a transaction fixes individual update scripts but not all of our
scripts have been 'fixed' yet or are likely to be.  Any chance of
backporting this fix?  Failing that is the code in GIT fixed of this bug
and stable yet?  Any sign of Bucardo v4.4.5?

> > When I do a bucardo_ctl status on any of my syncs, I always get a 
> > date in the future [...]
> A bug filing would be great.
Bug filed at http://bucardo.org/bugzilla/show_bug.cgi?id=56

Thanks,
Geoff