Re: BUG #9757: Why reclaim index deleted pages need twice vacuum

Thanks very much, but in rare condition, it's a small trouble.

------------------------------------------------------------
公益是一辈子的事, I'm Digoal , Just Do it！
德哥(Digoal.Zhou)
数据库技术经理
***********************************************
杭州斯凯网络科技有限公司
杭州市紫荆花路2号联合大厦B座11层
邮编: 310013
手机: +86 13484021953
座机: +86 571 89710948
QQ: 276732431
email: digoal(dot)zhou(at)mopo(dot)com
MSN: zzzqware(at)hotmail(dot)com
Blog: http://blog.163.com/digoal(at)126/
Github: https://github.com/digoal

From: Heikki Linnakangas
Date: 2014-03-31 14:54
To: digoal
CC: pgsql-bugs
Subject: Re: [BUGS] BUG #9757: Why reclaim index deleted pages need twice vacuum
On 03/28/2014 10:15 AM, digoal(at)126(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 9757
> Logged by: digoal.zhou
> Email address: digoal(at)126(dot)com
> PostgreSQL version: 9.3.3
> Operating system: CentOS 6.4 x64
> Description:
>
> When I'm testing a index page recycling, found that the index page must be
> two vacuum can be reused.

Yep, that's how the b-tree works [1]. A deleted page cannot be
immediately reused, because there might be concurrent scans that are
just about to visit the page. So when a page is deleted, i.e unlinked
from the tree, it is stamped with the next transaction ID, and left in
place. The next vacuum checks that the transaction ID is no longer
visible to anyone, which ensures that there are no transactions running
that might've seen a reference to that page. Only after that the page
can be reused.

That's quite pessimal; in most cases the page could be reused much
earlier, because it is a very tight window for vacuum to delete a page
just when a concurrent scan has read a link to the page and is about to
follow it. But B-tree pages are usually not deleted that often that it
would matter in practice, so there has been no effort to optimize it.

In short, it's not a bug :-).

[1]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/README#l283

- Heikki