On 11-11-19 03:05 AM, Maxim Boguk wrote:
> On Sat, Nov 19, 2011 at 9:31 AM, Maxim Boguk<maxim.boguk at gmail.com>  wrote:
>
> Now replication lagging for 9 hours with errors every 40-50 minutes:
>
> 2011-11-19 11:01:31 MSKERROR  remoteWorkerThread_1: "update
> "_sports".sl_setsync set     ssy_seqno = '5016038207', ssy_snapshot =
> '501051321:501051321:',     ssy_action_list = '' where ssy_setid in
> (1,9) and ssy_seqno<  '5016038207'; " 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.
>
> again on medium size (500.000) batch update.
>
> It seems 3x-node configuration with slony 2.0.7 + postgresql 9.1 quite
> unstable with batch updates.
>
> Is here everything that I can use to work it more smooth?
>
>

I  wonder out load if the slony remote worker needs to be in a 
serializable transaction or if READ COMMITTED is good enough.

I also wonder if making the remote listener threads explicit read only 
transactions would help.

The disorder tests against a 9.1 machine hit many of these read/write 
dependencies in a test run.   Slony just retries things and it 
eventually works but I can see how a 500,000 item batch means that you 
might often encounter an issue before finishing a SYNC.

My unverified guess is that changing this won't break slony but might 
effect applications querying the slave that depend on the serializable 
behaviour and maybe it should be a configuration parameter.

There are two places in remote_worker.c where it places the local 
connection to serializable.