Re: Transaction Snapshots and Hot Standby

On Thu, 2008-09-11 at 15:17 +0200, Jochem van Dieten wrote:
> On Thu, Sep 11, 2008 at 2:07 AM, Simon Riggs wrote:
> > Transaction snapshots is probably the most difficult problem for Hot
> > Standby to resolve.
>
> > * remotely from primary node
> > * locally on the standby node
> >
> > If we derive a snapshot locally, then we will end up with a situation
> > where the xmin of the local snapshot precedes the xmin of the primary
> > node. When this occurs it will then be possible for WAL records to
> > arrive on the standby that request removal of rows that a transaction
> > might wish to see. Preventing that situation can be done by either
> > deferring WAL apply or by cancelling queries.
>
> Which operations can request row removal? Isn't that just specific
> operations that have their own 'is this save to remove' calculations
> anyway (i.e. vacuum and HOT prune)?
>
> What I am thinking about is a design where the primary node were to
> regularly push an OldestXMin into the WAL, the WAL apply process on
> the standby nodes pushes it into shared memory and the backends keep
> an OldestMasterXMin in shared memory. The standby nodes then regularly
> pushes back the oldest OldestMasterXMin from all backends to the
> master. Vacuum and HOT prune could then base their calculations on an
> OldestXMin that is not the OldestXMin of the master itself, but of the
> master and the standby nodes. That way removal of records that are
> still visible on one of the standby nodes is prevented on the master
> instead of worked around on the standby nodes.
>
> The obvious downside would be bloat on the master (which could get out
> of hand if a slave is a few days behind due to a long report), but I
> think in terms of visibility and consistency this would work. Or am I
> completely misunderstanding the problem?

Yes, just sending the xmin from standby to primary is the best way of
having primary and standby work together. I will include this, thanks.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support