Skip site navigation (1) Skip section navigation (2)

Re: Hot Standby query cancellation and Streaming Replication integration

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Hot Standby query cancellation and Streaming Replication integration
Date: 2010-02-27 22:09:59
Message-ID: 4B899837.7070807@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Greg SmithDate: 2010-02-27 22:23:18
Subject: Re: Lock Wait Statistics (next commitfest)
Previous:From: Michael MeskesDate: 2010-02-27 21:56:02
Subject: Re: NaN/Inf fix for ECPG

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group