Thread: Re: default_transaction_isolation = serializable causes crash under Hot Standby

Simon Riggs  wrote:
> Kevin Grittner  wrote:
> 
>> But if you set it in the postgresql.conf file, it's not pretty:
>>
>> kevin@kevin-desktop:~$ psql -p 5433 test
>> psql: FATAL: can not create a serializable snapshot during
>> recovery
>>
>> Ideas?
> 
> The patch as submitted doesn't do anything useful for the case
> where the setting in .conf is serializable.
Precisely the point I was trying to make.  We discussed an approach,
I tried it, but that approach doesn't work.  So we need new ideas.
> 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?
> * throw a WARNING if serializable is stated in other cases, and
> downgrade the request to repeatable read
Yeah, that idea has been in the back of my mind all along, but I
couldn't find a comfortable way to reconcile that with the point
above and with the fact that we all seem to hope to some day have a
way to run true serializable transactions under HS.
> Throwing a WARNING is better than an ERROR, since it reduces the
> level of application editing to make it work for HS. Or better
> still, just document that serializable currently means repeatable
> read when executed on an HS.
Somehow it just "feels" wrong, but if nobody can propose a less
painful alternative, perhaps that is best.  For now.
> On another note, what happened to the plan to export regular
> serializable snapshots to the standby for use as deferred
> snapshots?
For the record, I think the most promising variation on that was to
include a new WAL record type or a bit in the WAL record header to
flag points in the WAL stream at which a transaction on HS could not
see any anomalies.  A serializable transaction on a HS would use a
snapshot built at that point, either by waiting for one or having the
WAL receiver keep the latest known safe one available.
That is still on my list of things I would like to do, but round
tuits are hard to come by these days, and there are a number of items
higher on that list.
-Kevin

On Sun, Apr 29, 2012 at 1:40 PM, Kevin Grittner
<Kevin.Grittner@wicourts.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