Re: max_standby_delay considered harmful

On Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> The only remaining option is to continue applying WAL until you reach
>> a point where no locks are held, then pause. But from a user's POV
>> that is nearly indistinguishable from simply setting
>> hot_standby_conflict_winner to in the first place I think.
>
> Not really, the use case would be using the slave as a reporting server,
> you know you have say 4 hours of reporting queries during which you will
> pause the recovery. So it's ok for the pause command to take time.

Seems like it could take FOREVER on a busy system. Surely that's not
OK. The fact that Hot Standby has to take exclusive locks that can't
be released until WAL replay has progressed to a certain point seems
like a fairly serious wart. We had a discussion on another thread of
how this can make the database fail to shut down properly, a problem
we're not addressing because we're too busy arguing about
max_standby_delay. In fact, if we knew how to pause replay without
leaving random locks lying around, we could rearrange the whole smart
shutdown sequence so that we paused replay FIRST and then waited for
all backends to exit, but the consensus on the thread where we
discussed this was that we did not know how to do that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company