[Bucardo-general] bucardo tables causing REINDEX fail

[Jonathan Brinkman]

Thanks for the reply. Stopping my production application is NOT an option,
at least not for a routine indexing. This is a security application and we
cannot take clients offline even for a couple of minutes. Losing Bucardo's
sync'ing for a couple of minutes is not a huge deal, but the database(s)
cannot be put in single-user mode for vacuum and reindex activities.
So I guess B5 is an important upgrade for us. 
I hope you test the reindex / vacuum activities on a busy bucardo master
server to ensure no disruptions occur. When our reindexing broke mid-way, it
screwed up all kinds of things, including bucardo, and forced a restart of
the database server.

-----Original Message-----
From: Rosser Schwarz [mailto:rosser.schwarz at gmail.com] 
Sent: Tuesday, April 10, 2012 5:21 PM
To: JB at blackskytech.com
Cc: Greg Sabino Mullane; bucardo-general at bucardo.org
Subject: Re: [Bucardo-general] bucardo tables causing REINDEX fail

On Tue, Apr 10, 2012 at 1:31 PM, Jonathan Brinkman <JB at blackskytech.com>
wrote:
> I guess I could bucardo_ctl stop, do the REINDEX, then restart bucardo.

The problem isn't Bucardo accessing the delta table and causing the
contention and deadlocks, but the triggers that are invoked in
response to your application's activity.  When you modify data, the
trigger(s) push references to the rows that were changed onto the
delta table, and then notify the Bucardo daemon that there's new data
to replicate.  The REINDEX is running into conflicts with those
triggers' access to the delta table, which is resulting in deadlocks.

Offhand, the only way I can think of to eliminate all contention over
B4's bucardo_delta is to stop your application or otherwise quiesce
its database activity for the duration of the REINDEX, as Greg
suggested.

That's one of the many things that B5 is aiming to fix: by using
separate delta tables for each user object that's being replicated,
instead of a common delta queue for everything, there's much less
contention over adding deltas to the queue.  (As for its availability,
I'll defer to Greg.  It's very stable and usable already, though, and
I believe a number of people have deployed it without significant
difficulty.)

rls

-- 
:wq