Re: BUG #14361: snapshot too old bug? in xmin>=xid transaction, also raise snapshot too old error

On Fri, Oct 7, 2016 at 10:03 AM, <digoal(at)126(dot)com> wrote:

> create table tbl1(id int);
> insert into tbl1 values (1);
>
> session a:
> postgres=# begin;
> BEGIN
> postgres=# insert into tbl1 values (2); -- generate xid, then vacuum cann't
> reclaim dead tuple after this xid.
> INSERT 0 1
> postgres=# with t as(select pg_sleep(100)) select * from tbl1,t; -- emulate
> a long query, it will visite dirty page after this query's snapshot->lsn.
>
>
> then , quick , generate a new lsn dirty page.
> session b:
> postgres=# update tbl1 set id=3;
>
>
> -- vacuum have not reclaim the deadtuple, but session a raise error the
> same.
> session a:
> ERROR: snapshot too old
>
> It's not perfect.

To recap the above: when a snapshot is in use long enough to be
past the old_snapshot_threshold it will generate a "snapshot too
old" error when it accesses a page modified since the snapshot was
taken -- whether or not the modification was caused by early
cleanup of data.

True, and not a bug. In fact, this is not dissimilar to the
situation that can occur in some other DBMS when the rollback log
is purged. There is not a risk of seeing incorrect results to your
query; but if the snapshot gets too old, there is indeed a risk of
getting a "snapshot too old" error even if keeping infinite data on
what modified which pages would allow the DBMS to know that it is
OK to allow the query to proceed without risk of incorrect results.
The point is to allow cleanup of exactly the information that
would *tell* you that it is OK to proceed without error.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company