Re: Reduce pinning in btree indexes

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Mar 13, 2015 at 8:52 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On 15 February 2015 at 00:19, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>> What they wanted was what happened in the other database
>>> product -- if a snapshot got sufficiently old that cleaning up
>>> the MVCC data was a problem *and* the snapshot was used again
>>> *and* it read a page which had been modified far enough back
>>> that it was not possible to return correct data, then they
>>> wanted to receive a "snapshot too old" error. I wrote a patch
>>> to do that...
>>
>> So, please lets see the patch. It seems useful for core
>> Postgres.
>
> It was submitted here:
>
> http://www.postgresql.org/message-id/136937748.3364317.1423964815320.JavaMail.yahoo@mail.yahoo.com

The feedback was generally fairly positive except for the fact that
snapshot "age" (for purposes of being too old) was measured in
transaction IDs assigned. There seemed to be a pretty universal
feeling that this needed to be changed to a time-based setting.
I've been working on that, although my initial efforts proved to be
a dead-end. I've had some off-list discussions with Andrew Dunstan
and we have agreed on something that looks like it should work
well, but it's still being coded.

The other thing that is needed is to sprinkle the other index AMs
with TestForOldSnapshot() calls. In the btree code the right spots
for those were all following BufferGetPage() calls, so the work was
in seeing where each of those could be called from to see whether
it was a spot that the check was needed.

Of course, docs and comment changes are also needed, but that's
probably only a day or two's effort.

Further discussion of the "snapshot too old" patch should probably
go on its thread. The spillover is largely my fault for
referencing one from the other.

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