Hot Standby query cancellation and Streaming Replication integration

On Mon, 2010-03-01 at 16:12 -0400, pgsql-hackers-owner(at)postgresql(dot)org
wrote:
> . . .
> However there is a concern with max_standby_age. If you set it to,
> say, 300s. Then run a 300s query on the slave which causes the slave
> to fall 299s behind. Now you start a new query on the slave -- it gets
> a snapshot based on the point in time that the slave is currently at.
> If it hits a conflict it will only have 1s to finish before the
> conflict causes the query to be cancelled.
>
> In short in the current setup I think there is no safe value of
> max_standby_age which will prevent query cancellations short of -1. If
> the slave has a constant stream of queries and always has at least one
> concurrent query running then it's possible that the slave will run
> continuously max_standby_age-epsilon behind the master and cancel
> queries left and right, regardless of how large max_standby_age is.
>
> To resolve this I think you would have to introduce some chance for
> the slave to catch up. Something like refusing to use a snapshot older
> than max_standby_age/2 and instead wait until the existing queries
> finish and the slave gets a chance to catch up and see a more recent
> snapshot. The problem is that this would result in very unpredictable
> and variable response times from the slave. A single long-lived query
> could cause replay to pause for a big chunk of max_standby_age and
> prevent any new query from starting.
>
> Does anyone see any way to guarantee that the slave gets a chance to
> replay and new snapshots will become visible without freezing out new
> queries for extended periods of time?

At the risk of looking foolish, I have a hand-wavy,
unlikely-to-be-possible, not-going-to-make-it-for-9.0, and maybe
unoriginal idea that I'll share.

As Greg has identified, no matter what max_standby_age you select, a
sequence of overlapping queries will eventually exceed the
max_standby_delay threshold, and tuples that your query depends on would
then be modified underneath you.

IIUC this is only a problem for WAL from HOT updates and vacuums. If no
vacuums or HOT updates have been performed, there is no risk of
returning bad data. So WAL that does not contain HOT updates or vacuums
could be applied on the standby without risk, even if there are
long-running queries in play. This is not a complete solution but may
reduce the likelihood of queries having to be cancelled. I guess the
approach here would be to check WAL before applying it, and only cancel
queries if the WAL contains HOT updates or vacuums.

Taking the idea further, if WAL records contained the tid of the latest
tuples that were overwritten, even more WAL could be applied without
having to cancel queries.

To take it further still, if vacuum on the master could be prevented
from touching records that are less than max_standby_delay seconds old,
it would be safe to apply WAL from the very latest vacuum. I guess HOT
could be handled similarly though that may eliminate much of the
advantage of HOT updates.

Apologies if this has already been covered, some of this discussion went
over my head.

/action Puts on asbestos underwear

__
Marc