Re: Re: Hot Standby query cancellation and Streaming Replication integration

Greg, Joachim,

> As I see it, the main technical obstacle here is that a subset of a
> feature already on the SR roadmap needs to get built earlier than
> expected to pull this off. I don't know about Tom, but I have no
> expectation it's possible for me to get up to speed on that code fast
> enough to contribute anything there. I expect the thing I'd be most
> productive at as far as moving the release forward is to continue
> testing this pair of features looking for rough edges, which is what I
> have planned for the next month.

That's OK with me. I thought you were saying that xmin-pub was going to
be easier than expected. Per my other e-mails, I think that we should
be shooting for "good enough, on time" for 9.0., rather than "perfect".
We can't ever get to "perfect" if we don't release software.

Quite frankly, simply telling people that "long-running queries on the
slave tend not to be effective, wait for 9.1" is a possibility. If you
consider the limitations and silent failures associated with MySQL
replication, let alone the issues with other Postgres solutions or the
replication of some of the nosql databases, "no long-running queries" is
a positively straightforwards restriction.

HS+SR is still a tremendous improvement over the options available
previously. We never thought it was going to work for everyone
everywhere, and shouldn't let our project's OCD tendencies run away from us.

> I've been down that road and it leads quickly to the following
> question: "how can I tell how old in time-based units an xid is?" If
> there were an easy answer to that question, vacuum_defer_cleanup_age
> would already be set in time units. It's the obvious UI to want, it's
> just not obvious how to build it internally. Maybe I missed something,
> but my guess is that vacuum_defer_cleanup_age is already as good as it's
> going to get.

Well, we could throw this on the user if we could get them some
information on how to calculate that number. For example, some way for
them to calculate the number of XIDs per minute via a query, and then
set vacuum_defer_cleanup_age appropriately on the master. Sure, it's
clunky, but we've already warned people that 9.0 will be clunky and hard
to administer. And it's no worse than setting FSM_pages used to be.

However, first we need to test that setting vacuum_defer_cleanup_age
actually benefits query cancel issues.

> We're not only canceling queries, we are effectively canceling
> transactions. It seems quite impossible to repeat all queries from a
> transaction that has started in the past. One query might be or
> include the result of a previous query and as the data we see now has
> changed since then, the client might now want to execute a different
> query when it gets a different result out of a previous query...

Sure, except that I don't expect people to be using explicit
transactions as much on the slaves, since they are read-only anyway and
can't even create temp tables. So having the retry not retry if there is
an explicit transaction would be an OK option.

> And even if it was possible, how often would you retry? You still have
> no guarantee that your query succeeds the second time. I'd claim that
> if a query failed once, chances are even higher that it fails again
> than that it succeeds the second time. Moreover if you continue to
> repeat the query and if queries come in at a certain rate, you need to
> process more and more queries on the slave which will not really help
> other queries to finish in time nor will it be beneficial for the
> throughput of the system as a whole...

Well, we'd need to have a limited number of retries, which means a GUC
in recovery.conf:

query_cancel_retry = #

This might default to, say, 2.

However, I'd still like to hear from someone with the requisite
technical knowledge whether capturing and retrying the current query in
a query cancel is even possible.

--Josh Berkus