Re: Hot Standby and handling max_standby_delay

Simon Riggs wrote:
> We need to calculate a more accurate time since WAL arrived to make
> max_standby_delay sensible in all cases. Difficult to know exactly when
> to record new timestamps for received WAL. So, proposal is...
>
> if (Base time is earlier than WAL record time)
> standby_delay = WAL record time - Base time
> else
> standby_delay = now() - Base time
>
> When standby_mode = off we record new base time when a new WAL file
> arrives.
>
> When standby_mode = on we record new base time each time we do
> XLogWalRcvFlush(). We also record a new base time on first entry to the
> main for loop in XLogRecv(), i.e. each time we start writing a new burst
> of streamed WAL data.
>
> So in either case, when we are waiting for new input we reset the timer
> as soon as new WAL is received. The resolution/accuracy of standby_delay
> will be no more than the time taken to replay a single file. This
> shouldn't matter, since sane settings of max_standby_delay are either 0
> or a number like 5-20 (seconds).

That would change the meaning of max_standby_delay. Currently, it's the
delay between *generating* and applying a WAL record, your proposal
would change it to mean delay between receiving and applying it. That
seems a lot less useful to me.

With the current definition, I would feel pretty comfortable setting it
to say 15 minutes, knowing that if the standby falls behind for any
reason, as soon as the connection is re-established or
archiving/restoring fixed, it will catch up quickly, blowing away any
read-only queries if required. With your new definition, the standby
would in the worst case pause for 15 minutes at every WAL file.

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