Re: max_standby_delay considered harmful

On Monday 10 May 2010 00:25:44 Florian Pflug wrote:
> 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.
The difference is that in HS you have to wait for a moment where *no exclusive
lock at all* exist, possibly without contending for any of them, while on the
master you might not even blocked by the existence of any of those locks.

If you have two sessions which in overlapping transactions lock different
tables exlusively you have no problem shutting the master down, but you will
never reach a point where no exclusive lock is taken on the slave.

Andres