Re: Reduce pinning in btree indexes

On Mon, Feb 23, 2015 at 2:48 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
>> Robert pointed out that the visibility information
>> for an index-only scan wasn't checked while the index page READ
>> lock was held, so those scans also still hold the pins.
>
> Why does an index-only scan need to hold the pin?

Suppose there's a dead tuple in the heap someplace, and an index
pointer pointing to that dead tuple. An index scan reaches the
relevant page and copies all of the index pointers. VACUUM removes
the index pointer and marks the heap page all-visible. The scan now
uses the index pointers copied to backend-local memory and notes that
the heap-page is all-visible, so the scan sees the tuple even though
that tuple is totally gone from the heap by that point. Holding the
pin prevents this, because we can't reach the second heap pass until
the scan is done with the page, and therefore the dead line pointer in
the heap page can't be marked unused, and therefore the page can't be
marked all-visible.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company