Re: max_standby_delay considered harmful

On May 9, 2010, at 21:04 , Simon Riggs wrote:
> On Sun, 2010-05-09 at 16:10 +0200, Florian Pflug wrote:
>
>> Adding pause/resume seems to introduce some non-trivial locking
>> problems, though. How would you handle a pause request if the recovery
>> process currently held a lock?
>
> (We are only talking about AccessExclusiveLocks here. No LWlocks are
> held across WAL records during replay)
>
> Just pause. There are no technical problem there.
>
> Perhaps a danger of unforeseen consequences, though doing that might
> also be desirable, who can say?

No technical problems perhaps, but some usability ones, no?

I assume people would pause recovery to prevent it from interfering with long-running reporting queries. Now, if those queries might block indefinitely if the pause request by chance was issued while the recovery process held an AccessExclusiveLock, then the pause *caused* exactly what it was supposed to prevent. Setting hot_standby_conflict_winner to "queries" would at least have allowed the reporting queries to finish eventually.

If AccessExclusiveLocks are taken out of the picture (they're supposed to be pretty rare on a production system anyway), setting hot_standby_conflict_winner to "queries" seems to act like a conditional pause request - recovery is paused as soon as it gets in the way. In this setting, the real advantage of pause would be to prevent recovery from using up all available IO bandwidth. This seems like a valid concern, but calls more for something like recovery_delay (similar to vacuum_delay) instead of pause().

best regards,
Florian Pflug