Обсуждение: BUG #14361: snapshot too old bug? in xmin>=xid transaction, also raise snapshot too old error

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM2MQpMb2dnZWQgYnk6ICAg
ICAgICAgIFpob3UgRGlnb2FsCkVtYWlsIGFkZHJlc3M6ICAgICAgZGlnb2Fs
QDEyNi5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjYuMApPcGVyYXRpbmcg
c3lzdGVtOiAgIENlbnRPUyA2LnggeDY0CkRlc2NyaXB0aW9uOiAgICAgICAg
CgpjcmVhdGUgdGFibGUgdGJsMShpZCBpbnQpOw0KaW5zZXJ0IGludG8gdGJs
MSB2YWx1ZXMgKDEpOw0KDQpzZXNzaW9uIGE6DQpwb3N0Z3Jlcz0jIGJlZ2lu
Ow0KQkVHSU4NCnBvc3RncmVzPSMgaW5zZXJ0IGludG8gdGJsMSB2YWx1ZXMg
KDIpOyAgLS0gZ2VuZXJhdGUgeGlkLCB0aGVuIHZhY3V1bSBjYW5uJ3QKcmVj
bGFpbSBkZWFkIHR1cGxlIGFmdGVyIHRoaXMgeGlkLiAgDQpJTlNFUlQgMCAx
DQpwb3N0Z3Jlcz0jIHdpdGggdCBhcyhzZWxlY3QgcGdfc2xlZXAoMTAwKSkg
c2VsZWN0ICogZnJvbSB0YmwxLHQ7ICAtLSBlbXVsYXRlCmEgbG9uZyBxdWVy
eSwgaXQgd2lsbCB2aXNpdGUgZGlydHkgcGFnZSBhZnRlciB0aGlzIHF1ZXJ5
J3Mgc25hcHNob3QtPmxzbi4gDQoNCg0KDQp0aGVuICwgcXVpY2sgLCBnZW5l
cmF0ZSBhIG5ldyBsc24gZGlydHkgcGFnZS4gICANCnNlc3Npb24gYjoNCnBv
c3RncmVzPSMgdXBkYXRlIHRibDEgc2V0IGlkPTM7DQoNCg0KLS0gdmFjdXVt
IGhhdmUgbm90IHJlY2xhaW0gdGhlIGRlYWR0dXBsZSwgYnV0IHNlc3Npb24g
YSByYWlzZSBlcnJvciB0aGUKc2FtZS4gIA0Kc2Vzc2lvbiBhOg0KRVJST1I6
ICBzbmFwc2hvdCB0b28gb2xkDQoNCkl0J3Mgbm90IHBlcmZlY3QuICAgDQoN
Cg0KYmVzdCByZWdhcmRzLA0KZGlnb2FsDQpgYGAKCg==

On Fri, Oct 7, 2016 at 10:03 AM,  <digoal@126.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