On Tue, Nov 22, 2011 at 10:26 PM, Steve Singer <ssinger at ca.afilias.info> wrote:
> On 11-11-20 05:47 AM, Cédric Villemain wrote:
>>
>> 2011/11/20 Steve Singer<ssinger at ca.afilias.info>:
>>>
>>> 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 attached patch makes the remoteListener read only and seems to eliminate
> the pivot situations involving sl_event and sl_confirm.

The attached patch doesn't work because DEFERRABLE only works in
conjunction with serializable mode.
New version of patch attached to do this.

> Making the remote worker READ COMMITTED seemed to break things.

The error message generated by serialization is
ERRCODE_T_R_SERIALIZATION_FAILURE, raised at level ERROR. So the
"FATAL" error is being generated by Slony not by Postgres.

ISTM that setting the remote worker to REPEATABLE READ would work well
for this case. Patch attached.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services