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.


-- 
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/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не все.