Skip site navigation (1) Skip section navigation (2)

BUG #5932: CLUSTER doesn't update n_dead_tup

From: "Andy Lester" <andy(at)petdance(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5932: CLUSTER doesn't update n_dead_tup
Date: 2011-03-15 22:23:14
Message-ID: 201103152223.p2FMNEZC035008@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5932
Logged by:          Andy Lester
Email address:      andy(at)petdance(dot)com
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)

Responses

pgsql-bugs by date

Next:From: rajeshDate: 2011-03-16 07:10:26
Subject: BUG #5933: database restore error
Previous:From: Tom LaneDate: 2011-03-15 18:43:40
Subject: Re: SELECT '(1, nan, 3)'::cube;

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group