[Bucardo-general] indirect bucardo problem - stuck triggers?

[Paul Theodoropoulos]

Our staging database ballooned up overnight from 4gb to 12gb, causing an 
out-of-space issue. After much investigation, it seems to have been a 
bucardo problem, indirectly - found this while vacuuming, which removed 
all the extra disk use:

INFO:  vacuuming "bucardo.bucardo_truncate_trigger"
INFO:  "bucardo_truncate_trigger": found 623880 removable, 181642 
nonremovable row versions in 1111573 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 3.32s/1.81u sec elapsed 30.57 sec.

I hadn't looked at the bucardo logs initially, since it didn't seem 
logical that bucardo would cause the local db to balloon up (the remote 
db was perfectly normal in size at the same time).

Checking the bucardo logs, apparently I had set my shared_buffers too 
high in relation to the system max  (I had increased shared_buffers the 
day before when we were getting lots of transactions hanging)

(31838) [Thu Apr 24 07:48:48 2014] KID (trumgr_main_sync) Kid 31838 
exiting at cleanup_kid. Sync "trumgr_main_sync" 
public.group_stats_main_history_99 Reason: DBD::
Pg::st execute failed: ERROR:  out of shared memory HINT: You might need 
to increase  max_pred_locks_per_transaction. CONTEXT:  SQL statement 
"       SELECT  1
   FROM    bucardo.delta_public_unit_checkins d WHERE   NOT EXISTS 
(         SELECT 1         FROM bucardo.track_public_unit_checkins 
t         WHERE  d.
txntime = t.txntime         AND    (t.target = 'dbgroup 
trumgr_main_dbs'::text OR t.target ~ '^T:')       ) LIMIT 1" PL/pgSQL 
function "bucardo_delta_check" line 24
  at EXECUTE statement at /usr/local/share/perl/5.10.1/Bucardo.pm line 
3160. Line: 4920 Main DB state: ? Error: none DB local_trumgr_main 
state: 53200 Error: 7 DB remote_trumgr_main state: ? Error: none

I reduced shared buffers, and set max_pred_locks_per_transaction higher 
than default (normal is 64, I made it 128) - and that seems like it may 
have done the trick.

I realize this is more of a boneheaded configuration problem on my side, 
but it was interesting that it caused the truncate_triggers to go bonkers.

-- 
Paul Theodoropoulos
www.anastrophe.com