The following bug has been logged online:
Bug reference: 5932
Logged by: Andy Lester
Email address: [email protected]
PostgreSQL version: 9.0
Operating system: Linux
Description: CLUSTER doesn't update n_dead_tup
Details:
The CLUSTER command does not update the results coming back from
pg_stat_get_dead_tuples().
Running a VACUUM VERBOSE on the table after CLUSTERing shows that no tuples
are removed by the VACUUM, and the pg_stat_get_dead_tuples() then correctly
returns 0.
Here is a test program and its output that demonstrates.
$ cat cluster-bug.sql
drop table if exists foo;
create table foo ( x integer );
insert into foo values ( 1 );
insert into foo values ( 2 );
insert into foo values ( 3 );
create index foo_foo on foo(x);
cluster foo using foo_foo;
select pg_sleep(1);
select pg_stat_get_dead_tuples( 'foo'::regclass ), 'After 1st cluster,
before delete';
delete from foo where x = 2;
select pg_sleep(1);
select pg_stat_get_dead_tuples( 'foo'::regclass ), 'After delete, before 2nd
cluster, should have 1 dead row';
cluster foo;
select pg_sleep(1);
select pg_stat_get_dead_tuples( 'foo'::regclass ), 'After 2nd cluster,
before vacuum, should have 0 dead rows';
vacuum verbose foo;
select pg_sleep(1);
select pg_stat_get_dead_tuples( 'foo'::regclass ), 'After vacuum, should
still have 0 dead rows';
$ psql -X -f cluster-bug.sql
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE INDEX
CLUSTER
pg_sleep
----------
(1 row)
pg_stat_get_dead_tuples | ?column?
-------------------------+----------------------------------
0 | After 1st cluster, before delete
(1 row)
DELETE 1
pg_sleep
----------
(1 row)
pg_stat_get_dead_tuples | ?column?
-------------------------+--------------------------------------------------
--------
1 | After delete, before 2nd cluster, should have 1
dead row
(1 row)
CLUSTER
pg_sleep
----------
(1 row)
pg_stat_get_dead_tuples | ?column?
-------------------------+--------------------------------------------------
---------
1 | After 2nd cluster, before vacuum, should have 0
dead rows
(1 row)
psql:cluster-bug.sql:24: INFO: vacuuming "public.foo"
psql:cluster-bug.sql:24: INFO: index "foo_foo" now contains 2 row versions
in 2 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.
psql:cluster-bug.sql:24: INFO: "foo": found 0 removable, 2 nonremovable row
versions in 1 out of 1 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.
VACUUM
pg_sleep
----------
(1 row)
pg_stat_get_dead_tuples | ?column?
-------------------------+---------------------------------------------
0 | After vacuum, should still have 0 dead rows
(1 row)
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
