Re: Hot Standby query cancellation and Streaming Replication integration

Josh Berkus wrote:

> Now that I think about it, the xmin thing really doesn't seem
> conceptually difficult. If the slave just opens a 2nd, special query
> connection back to the master and publishes its oldest xmin there, as
> far as the master is concerned, it's just another query backend.
> Could it be that easy?
>

Something just like that is in fact already suggested as a workaround in
the Hot Standby manual:

"The first option is to connect to the primary server and keep a query
active for as long as needed to run queries on the standby. This
guarantees that a WAL cleanup record is never generated and query
conflicts do not occur, as described above. This could be done using
contrib/dblink and pg_sleep(), or via other mechanisms."

And the idea of doing it mainly in client land has its attractions.

The main reason I wandered toward asking about it in the context of SR
is that there's already this open "Standby delay on idle system" issue
with Hot Standby, and the suggested resolution for that problem involves
publishing keep-alive data with timestamps over SR. While all these
problems and potential solutions have been floating around for a long
time, as you pointed out, the little flash of insight I had here was
that it's possible to bundle these two problems together with a combined
keep-alive timestamp+xmin message that goes in both directions. That
removes one serious Hot Standby issue altogether, and adds an additional
conflict avoidance mechanism for people who want to enable it, all with
something that needs to get done sooner or later anyway for sync rep.

The part I still don't have good visibility on is how much of the
necessary SR infrastructure needed to support this communications
channel is already available in some form. I had though the walsender
on the master was already receiving messages sometimes from the
walreceiver on the standby, but I'm getting the impression from Heikki's
comments that this not the case at all yet.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us