Re: max_standby_delay considered harmful

On May 9, 2010, at 22:01 , Robert Haas wrote:
> 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.

If this is a serious wart then it's not one of hot standby, but one of postgres proper. AccessExclusiveLocks (SELECT-blocking locks that is, as opposed to UPDATE/DELETE-blocking locks) are never necessary from a correctness POV, they're only there for implementation reasons.

Getting rid of them doesn't seem completely insurmountable either - just as multiple row versions remove the need to block SELECTs dues to concurrent UPDATEs, multiple datafile versions could remove the need to block SELECTs due to concurrent ALTERs. But people seem to live with them quite well, judged from the amount of work put into getting rid of them (zero). I therefore fail to see why they should pose a significant problem in HS setups.

> 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.

Yeah, this was exactly my line of thought too.

best regards,
Florian Pflug