Re: default_transaction_isolation = serializable causes crash under Hot Standby

On Sun, Apr 29, 2012 at 1:40 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

>> IMHO the desired behaviour would be
>>
>> * prevent default_transaction_isolation = serializable as a default
>> setting when we enter Hot Standby by throwing a FATAL error from
>> the startup process. I can help implement that if we agree.
>
> I'm not sure how well that plays for all users of HS.  It would work
> for us because there are a number of settings we want to change on a
> HS; we use a different postgresql.conf from the master, so this works
> fine.  I am concerned about people who want to promote the HS to a
> new master without swapping in a different conf file and doing a
> reload or restart -- if they want serializable transactions as the
> default on their master, how would this work?

I can see you might want to have a standby with hot_standby=off that
was immediately ready to take the place of the master. In that case,
the setting of default_transaction_isolation would have no effect on
the standby, so you are ready and waiting.

If you request hot_standby=on presumably you're not going to want a
continuous stream of ERRORs. Blocking that completely is hard because
we would need to test the parameter file as well as testing user or
database settings.

The only way default_transaction_isolation = serializable would be
acceptable when hot_standby = on is if we silently downgrade the
isolation level to read committed. That way everything just works,
albeit not quite as requested. So I think that's the best way
forwards.

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