Re: Hot Standby query cancellation and Streaming Replication integration

Bruce Momjian wrote:
>> "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."
>>     
>
> I am unclear how you would easily advance the snapshot as each query
> completes on the slave.
>   

The idea of the workaround is that if you have a single long-running 
query to execute, and you want to make sure it doesn't get canceled 
because of a vacuum cleanup, you just have it connect back to the master 
to keep an open snapshot the whole time.  That's basically the same idea 
that vacuum_defer_cleanup_age implements, except you don't have to 
calculate a value--you just hold open the snapshot to do it.

When that query ended, its snapshot would be removed, and then the 
master would advance to whatever the next latest one is.  Nothing 
fancier than that.  The only similarity is that if you made every query 
that happened on the standby do that, it would effectively be the same 
behavior I'm suggesting could be available via the standby->master xmin 
publication.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us