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(at)2ndQuadrant(dot)com www.2ndQuadrant.us