Re: max_standby_delay considered harmful

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.

Ok, now that we've gotten over that, here's another proposal for what a
delay setting could look like. Let's have a setting similar to
statement_timeout, that specifies how long a statement is allowed to run
until it becomes subject to killing if it conflicts with recovery
(actually, it would have to be a per-transaction setting, at least in
serializable mode). This would be similar to Tom's proposal, and it
would have the same drawback that it would give no guarantee on how much
the standby can fall behind. However, it would be easier to understand:
a query gets to run for X seconds, and after that it will be killed if
it gets in the way.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com