BUG #9757: Why reclaim index deleted pages need twice vacuum
От | digoal@126.com |
---|---|
Тема | BUG #9757: Why reclaim index deleted pages need twice vacuum |
Дата | |
Msg-id | 20140328081525.1150.60443@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9757: Why reclaim index deleted pages need twice
vacuum
(Heikki Linnakangas <hlinnakangas@vmware.com>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 9757 Logged by: digoal.zhou Email address: digoal@126.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. The reason is thatVacuum for the first time, the main fork freespace map file will be generated, but do not generate the index of free space map file. THE DETAIL : digoal=# create extension pageinspect; CREATE EXTENSION digoal=# create table test(id int primary key, info text, crt_time timestamp); CREATE TABLE digoal=# insert into test select generate_series(1,5000000),md5(random()::text),clock_timestamp(); INSERT 0 5000000 digoal=# delete from test where id<>5000000; DELETE 4999999 -- the first vacuum, deleted index page can't reuse. digoal=# vacuum verbose analyze test; INFO: vacuuming "public.test" INFO: scanned index "test_pkey" to remove 4999999 row versions DETAIL: CPU 0.00s/1.75u sec elapsed 1.77 sec. INFO: "test": removed 4999999 row versions in 11628 pages DETAIL: CPU 0.00s/0.19u sec elapsed 0.22 sec. INFO: index "test_pkey" now contains 1 row versions in 3404 pages DETAIL: 4999999 index row versions were removed. 3398 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": found 4999999 removable, 1 nonremovable row versions in 11628 out of 11628 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.01s/2.83u sec elapsed 2.91 sec. INFO: vacuuming "pg_toast.pg_toast_16432" INFO: index "pg_toast_16432_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_16432": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.test" INFO: "test": scanned 11628 of 11628 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows VACUUM -- and no index fsm page. digoal=# select * from fsm_page_contents(get_raw_page('test_pkey','fsm',0)); ERROR: could not open file "base/16384/16471_fsm": No such file or directory digoal=# select * from fsm_page_contents(get_raw_page('test_pkey','fsm',1)); ERROR: could not open file "base/16384/16471_fsm": No such file or directory digoal=# select * from fsm_page_contents(get_raw_page('test_pkey','fsm',2)); ERROR: could not open file "base/16384/16471_fsm": No such file or directory -- but table fsm page generated. digoal=# select * from fsm_page_contents(get_raw_page('test','fsm',0)); fsm_page_contents ------------------- 0: 244 + 1: 244 + 3: 244 + 7: 244 + .....so on -- the second vacuum. digoal=# vacuum verbose analyze test; INFO: vacuuming "public.test" INFO: index "test_pkey" now contains 1 row versions in 3404 pages DETAIL: 0 index row versions were removed. 3400 index pages have been deleted, 3400 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "test": found 0 removable, 0 nonremovable row versions in 0 out of 11628 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "pg_toast.pg_toast_16432" INFO: index "pg_toast_16432_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_16432": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.test" INFO: "test": scanned 11628 of 11628 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows VACUUM -- generate the index fsm pages. and this time , the deleted index page can reuse. digoal=# select * from fsm_page_contents(get_raw_page('test_pkey','fsm',0)); fsm_page_contents -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 255 + 0: 255 + 1: 255 + 3: 7:255 +
В списке pgsql-bugs по дате отправления:
Следующее
От: digoal@126.comДата:
Сообщение: BUG #9759: whe extend some varchar cols to varchar(n) , index filenode disappear, and 58P01 ERROR