Re: BUG #13770: Extending recovery_min_apply_delay on Standby causes it to be unavailable for a while

On Wed, Dec 30, 2015 at 10:05 PM, Greg Clough <greg(at)gclough(dot)com> wrote:
> I figured that if the database was in a consistent state when it was
> shutdown with a 1 hour delay, then when it was restarted with a new 24 hour
> delay then it would also be consistent and thus available for read-only
> work. In my test case, no transactions have occurred but the standby still
> wasn't accessible.

Are you sure about that? What does pg_xlogdump tells you? It seems
based on the information given upthread that there was at least one
transaction commit between the redo point and the consistent point.
When a standby is restarted, it would redo the replay from the last
same redo point, so that's just logic that the standby keeps being
unavailable for connections.
.
> I believe the unavailability of the standby for extended periods if the
> recovery_min_apply_delay is increased will create some confusion, just as it
> confused me initially. I can see two schools of thought when the parameter
> is increased:
>
> 1. The standby includes transactions that are newer than the
> "recovery_min_apply_delay" setting, so it must be prevented from exposing
> any data to ensure applications don't see data too soon.
> 2. The standby is consistent, so it should be available for read-only
> queries... but any new WAL should not be applied until the commit time is >
> recovery_min_apply_delay.

If the standby has a transaction commit only after a consistent point
is reached, you will be good to connect anyway.

> Obviously I fall into camp #2, where I expected the database to basically
> pause application of WAL until it passed the recovery_min_apply_delay... but
> still be available for read-only queries.
>
> If the preferred option is #1, then could we introduce a new error message
> so that it's a bit more communicative. Maybe something like:
>
> psql: FATAL: the database system has transactions newer than
> recover_min_apply_delay. Waiting...

Hm. The current error message depends on the state of the database
reported by the postmaster. It does not seem to me a good idea to
expose a new state at this level regarding recover_min_apply_delay.

In short, it seems to me that the correct way to address your concerns
is actually a documentation addition, the point being to mention that
a hot standby would take a longer time to become available for
read-only connections, aka allowing read-only connections if there are
transaction commits that happened between the redo point and the
consistent point.
--
Michael