Re: snapshot too old, configured by time

On Sat, Apr 23, 2016 at 5:20 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? 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.
>

I'm not sure that we should rely that much on Oracle behavior. It has very
different MVCC model.
Thus we can't apply same features one-by-one: they would have different pro
and cons for us.

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? If the former, does
> Kevin's new code allow us to do the later?

That makes sense for me. If we could improve read-only queries on slaves
this way, Kevin's new code becomes much more justified.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company