2011/11/20 Steve Singer <ssinger at ca.afilias.info>:
> 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.

if it is a read only transaction, then it is preferable to set it explicitely.
If I well understood Heikki, it should remove 'pivot' situation. See
http://wiki.postgresql.org/images/4/4f/SSI-PGConfEU2011.pdf (slide 22
for the immediate suggestions)

>
> 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.
>
>
>
>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>



-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation