BUG #9751: PostgreSQL free btree index page cann't reuse bug?

Поиск
Список
Период
Сортировка
От digoal@126.com
Тема BUG #9751: PostgreSQL free btree index page cann't reuse bug?
Дата
Msg-id 20140328035148.13967.60767@wrigleys.postgresql.org
обсуждение исходный текст
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      9751
Logged by:          digoal.zhou
Email address:      digoal@126.com
PostgreSQL version: 9.3.3
Operating system:   CentOS 6.4 x64
Description:

PostgreSQL free btree index page cann't reuse in the future?
This is my test :
digoal=# create table test(id int primary key, info text, crt_time
timestamp);
CREATE TABLE
digoal=# insert into test select
generate_series(1,50000000),md5(random()::text),clock_timestamp();
INSERT 0 50000000
digoal=# \dt+ test
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 3634 MB |
(1 row)
digoal=# \di+
                           List of relations
 Schema |   Name    | Type  |  Owner   | Table |  Size   | Description
--------+-----------+-------+----------+-------+---------+-------------
 public | test_pkey | index | postgres | test  | 1063 MB |
(1 row)
digoal=# copy (select * from test where id<>50000000) to
'/ssd3/pg93/test.dmp' with (format csv, delimiter ',', null '\N', quote '"',
force_quote *) ;
digoal=# delete from test where id<50000000;
DELETE 49999999
-- The vacuum result tell us index item removed , and it's free.
digoal=# vacuum verbose analyze test;
INFO:  vacuuming "public.test"
INFO:  scanned index "test_pkey" to remove 49999999 row versions
DETAIL:  CPU 0.17s/20.02u sec elapsed 23.89 sec.
INFO:  "test": removed 49999999 row versions in 116279 pages
DETAIL:  CPU 0.43s/1.34u sec elapsed 2.95 sec.
INFO:  index "test_pkey" now contains 1 row versions in 34023 pages
DETAIL:  49999999 index row versions were removed.
33990 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": found 49999999 removable, 1 nonremovable row versions in
116280 out of 116280 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 1.14s/29.67u sec elapsed 36.84 sec.
INFO:  vacuuming "pg_toast.pg_toast_34315"
INFO:  index "pg_toast_34315_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_34315": 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 30000 of 116280 pages, containing 0 live rows and
14680 dead rows; 0 rows in sample, 1 estimated total rows
VACUUM
-- but when i load the data into test table, the index bloat up two times
than the first index size.
digoal=# copy test from '/ssd3/pg93/test.dmp' with (format csv, delimiter
',', null '\N', quote '"');
COPY 49999999
digoal=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 3634 MB |
(1 row)
digoal=# \di+
                           List of relations
 Schema |   Name    | Type  |  Owner   | Table |  Size   | Description
--------+-----------+-------+----------+-------+---------+-------------
 public | test_pkey | index | postgres | test  | 2425 MB |
(1 row)
-- Why?
-- it's a bug?
-- and index page will reuse when the page free?

В списке pgsql-bugs по дате отправления:

Предыдущее
От: sdl@mnppsaturn.ru
Дата:
Сообщение: BUG #9756: Inconsistent database after OS restart
Следующее
От: clime7@gmail.com
Дата:
Сообщение: BUG #9749: ERROR: unexpected classid 3600