Re: snapshot too old, configured by time

On Sat, Apr 23, 2016 at 7:50 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Sat, Apr 23, 2016 at 12:48:08PM +0530, Amit Kapila wrote:
> > On Sat, Apr 23, 2016 at 8:34 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > >
> > > I kind of agreed with Tom about just aborting transactions that held
> > > snapshots for too long, and liked the idea this could be set per
> > > session, but the idea that we abort only if a backend actually touches
> > > the old data is very nice. I can see why the patch author worked hard
> > > to do that.
> > >
> > > How does/did Oracle handle this?
> > >
> >
> > IIRC then Oracle gives this error when the space in undo tablespace (aka
> > rollback segment) is low. When the rollback segment gets full, it
overwrites
> > the changed data which might be required by some old snapshot and when
that old
> > snapshot statement tries to access the data (which is already
overwritten), it
> > gets "snapshot too old" error. Assuming there is enough space in
rollback
> > segment, Oracle seems to provide a way via Alter System set
undo_retention =
> > <time_in_secs>.
> >
> > Now, if the above understanding of mine is correct, then I think the
current
> > implementation done by Kevin is closer to what Oracle provides.
>
> But does the rollback only happen if the long-running Oracle transaction
> tries to _access_ specific data that was in the undo segment, or _any_
> data that potentially could have been in the undo segment?
>

It does when long running transaction tries to access specific data. If
you want to know in more detail then you can read slides 7~29 from the
attached presentation (with focus on slides 28 and 29).

> If the
> later, it seems Kevin's approach is better because you would have to
> actually need to access old data that was there to be canceled, not just
> any data that could have been overwritten based on the xid.
>
> Also, it seems we have similar behavior already in applying WAL on the
> standby --- we delay WAL replay when there is a long-running
> transaction. Once the time expires, we apply the WAL. Do we cancel the
> long-running transaction at that time, or wait for the long-running
> transaction to touch some WAL we just applied?
>

As per my understanding, the error is given when any transaction tries to
access the data.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com