Re: max_standby_delay considered harmful

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> Comments?
>
> There's currently three ways to set max_standby_delay:
>
> max_standby_delay = -1 # Query wins
> max_standby_delay = 0 # Recovery wins
> max_standby_delay > X # Query wins until lag > X.
>
> As Tom points out, the 3rd option has all sorts of problems. I very much
> like the behavior that max_standby_delay tries to accomplish, but I have
> to agree that it's not very reliable as it is. I don't like Tom's
> proposal either; the standby can fall behind indefinitely, and queries
> get a varying grace period.
>
> Let's rip out the concept of a delay altogether, and make it a boolean.
> If you really want your query to finish, set it to -1 (using the current
> max_standby_delay nomenclature). If recovery is important to you, set it
> to 0.

I can't help but insisting on it, sorry. But.

The obvious solution to this problem for me is that to either make the
boolean reload friendly or to have pause/resume recovery. Ideally, both.

Then the default setting would be recovery wins, you pause the standby
replaying to ensure your query runs to completion. Very crude setting,
but 9.0 would offer easy to setup slave for *either* HA *or* off-load,
and a way to mitigate somehow.

The automated educated conflict solving based on some sort of timeout
running for one or all the current queries seems much harder to agree
upon when compared to applying existing code we tough we wouldn't yet
need. Let's revisit that decision: it seems to me we need it for 9.0.

Regards,
--
dim