On Sat, Apr 23, 2016 at 7:50 PM, Bruce Momjian <bruce@momjian.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@momjian.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.