As reported on the mailing list http://lists.slony.info/pipermail/slony1-general/2011-November/011939.htmllony 2.0.7 replication just stalled on big update on master (like 300.000 in batch.. nothing crazy). Slony log on slave show repeatable (like each 5 minute) error: 2011-11-18 08:46:03 MSKERROR remoteWorkerThread_4: "insert into "_sports".sl_event (ev_origin, ev_seqno, ev_timestamp, ev_snapshot, ev_type ) values ('4', '5000054127', '2011-11-18 06:18:44.863299', '922722:922722:', 'SYNC'); insert into "_sports".sl_confirm (con_origin, con_received, con_seqno, con_timestamp) values (4, 3, '5000054127', now()); insert into "_sports".sl_event (ev_origin, ev_seqno, ev_timestamp, ev_snapshot, ev_type ) values ('4', '5000054128', '2011-11-18 06:18:45.890297', '922731:922731:', 'SYNC'); insert into "_sports".sl_confirm (con_origin, con_received, con_seqno, con_timestamp) values (4, 3, '5000054128', now()); insert into "_sports".sl_event (ev_origin, ev_seqno, ev_timestamp, ev_snapshot, ev_type ) values ('4', '5000054129', '2011-11-18 06:18:46.921278', '922736:922736:', 'SYNC'); insert into "_sports".sl_confirm (con_origin, con_received, con_seqno, con_timestamp) values (4, 3, '5000054129', now()); commit transaction;" PGRES_FATAL_ERROR ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during write. HINT: The transaction might succeed if retried. In that case slony just terminate without commiting batch (and returned to the life only by watchdog).
Created attachment 138 [details] Proposed fix 9.1 to avoid pivot conflicts. specify SERIALIZABLE for read only deferred transactions. Make remote_worker calls to the provider read-only deferred Make remote_worker calls on the local con READ COMMITTED. Make the remote listener run in a READ ONLY DEFERRED transaction on Based on a patch by Simon Riggs.
http://git.postgresql.org/gitweb?p=slony1-engine.git;a=commitdiff;h=c94cd25269f45a614f6bf3b77c7b759d1de71bed http://git.postgresql.org/gitweb?p=slony1-engine.git;a=commitdiff;h=44aa04906a195b90a9e5fd6552d6fe097bc5a50d http://git.postgresql.org/gitweb?p=slony1-engine.git;a=commitdiff;h=e689d4fa5f1b4b0eeb9e3f2061b8ba29e5729bb7 Applied against 2.0 2.1 and master(2.2)