Re: BUG #14360: snapshot too old bug? cann't reclaim dead tuple after has only xmin's transaction.

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

> [tuple visible to "old snapshot" is vacuumed early, but not at
> the first vacuum where the age could allow it]

Not a bug.

Note that the documentation here:

https://www.postgresql.org/docs/9.6/static/runtime-config-resource.html#GUC-OLD-SNAPSHOT-THRESHOLD

Says:

| This setting does not attempt to guarantee that an error will be
| generated under any particular circumstances.

To perform the early cleanup more eagerly would require more
locking of internal structures, which would cause a noticeable
performance hit. Since the point of this setting is to improve
performance (by limiting bloat), there is a trade-off; heuristics
are used to balance the need for cleanup with the need to avoid as
much lock contention as possible.

If you have a realistic workload which allows enough bloat to have
a significant affect on performance because this heuristic seems
not to be striking the right balance, I would be interested in
seeing the details; but this simple case seems to get there quickly
enough to not pose such a risk.

Even in such a case, it would likely fall more under the rubric of
tuning than something that would be considered a bug. To be a bug,
it would need to have some affect when disabled, prune away tuples
modified by a transaction which is still active, generate an error
on a snapshot not past the threshold, generate an error on access
to a page which was not modified after the snapshot was taken, or
something similar.

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