Re: snapshot too old, configured by time

On Sat, Apr 23, 2016 at 8:34 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Tue, Apr 19, 2016 at 07:38:04AM -0400, Robert Haas wrote:
> > 2. Without this feature, you can kill sessions or transactions to
> > control bloat, but this feature is properly thought of as a way to
> > avoid bloat *without* killing sessions or transactions. You can let
> > the session live, without having it generate bloat, just so long as it
> > doesn't try to touch any data that has been recently modified. We
> > have no other feature in PostgreSQL that does something like that.
>
> 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.

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