Re: max_standby_delay considered harmful

On Wed, May 5, 2010 at 7:18 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Heikki Linnakangas wrote:
>> 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.
>>
>> If you have the monitoring in place to sensibly monitor the delay
>> between primary and standby, and you want a limit on that, you can put
>> together a script to flip the switch in postgresql.conf if the standby
>> falls too much behind.
>>
>> It would be nice to make that settable per-session, BTW. Though as soon
>> as you have one session using -1, the standby could fall behind. Still,
>> it might be useful if you run both kinds of queries on the same standby.
>
> +1 for a boolean
>
> We are not supposed to be designing the behavior during beta, which is
> exactly what we are doing, and I don't think we even know what behavior
> we want, let alone have we implemented it.  I think a boolean is very
> clear and it gives you the chance to optimize _one_ case, which is
> enough for 9.0.  Let's revisit this for 9.1 when we will know a lot more
> than we do now.

The existing behavior is probably not optimal, but I'm not seeing what
benefit we get out of neutering it.

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