On Sat, Nov 19, 2011 at 9:31 AM, Maxim Boguk <maxim.boguk at gmail.com> wrote:
> On Sat, Nov 19, 2011 at 5:26 AM, Christopher Browne
> <cbbrowne at afilias.info> wrote:
>> On Thu, Nov 17, 2011 at 11:56 PM, Maxim Boguk <maxim.boguk at gmail.com> wrote:
>>> 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).
>>
>> That seems like one of the sorts of cases that we could expect there
>> being with the new "full serialization" support in 9.1.
>>
>> Terminating the batch is not notably disastrous; a retry should work fine.
>
> After replica got lagged more then 3 hours (and slony getting
> deadlocked every 5 minutes or so,
>  as a result - large batch just do not go to through) I decided that
> way don't work.
>
> It seems chance getting into deadlock in my case very close to 100%.
>
> After some attempts I found only one way to deal with that issue:
> stop slony on second replica and on the master db for time sufficient
> to the db chew that large batch (stopping only on master or only on
> second replica was not enough).
>
> So that problem can be really painful and require manual DBA actions.
>

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?


-- 
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim.boguk at gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все.