Re: Allow "snapshot too old" error, to prevent bloat

Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Kevin Grittner (kgrittn(at)ymail(dot)com) wrote:

>> With the two patches I submitted, bloat stabilized at less than 5%
>> except for some btree indexes which followed pattern of inserting
>> at the end and deleting most (but not all) of the entries over
>> time. I've been working on a separate patch for that, but it's not
>> ready to present, so I probably won't post that until the first CF
>> of the next release -- unless someone's interested enough to want
>> to discuss it before then.
>
> Understood. I'd encourage you to post your thoughts on it- I'm
> certainly interested in it, even if it isn't something we'll be able to
> really work on for 9.5.

OK, if you're up for discussing it even while it's in early
development, I'll start a separate thread to outline the idea. As
a preliminary "teaser" in very abbreviated form, the idea is to
basically use the "page split" logic, but splitting all tuples from
multiple adjacent pages onto a new page to the right. The old tuples
maintain their pointers (to support scans positioned in that range),
but all pointers *to* those pages will be redirected to the new
page. Let's not bifurcate this thread by discussing it further
here; please wait until I start the new thread, which will be after
I post the next version of this "snapshot too old" patch.

> Isn't there a concern about the index entry disappearing also though?
> If there is a specific query against a particular value in the index and
> the index no longer has that value then the query would return zero
> results- but this could happen without visiting the heap, right?

Right; that's why I don't see how to avoid touching the index AMs.
The index pages also contain the LSN, and if you are not visiting
the heap it is because some page in the index was changed, and you
will see the "too recent" LSN there.

> Therefore, how do you know that the value was, or wasn't, visible at the
> time that the original long-running snapshot was taken.

Strictly speaking, you don't know anything that specific. You know
that changes were made that *might* have removed tuples that would
have been visible to the snapshot. Once a snapshot passes the
threshold for being considered "old", then viewing any page changed
after the snapshot was built has to trigger the error. That means
some false positives, but as long as you can't get false negatives
there is never a silently incorrect query result.

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