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

Re: BUG #5932: CLUSTER doesn't update n_dead_tup

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Andy Lester <andy(at)petdance(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5932: CLUSTER doesn't update n_dead_tup
Date: 2011-09-05 20:19:48
Message-ID: 201109052019.p85KJm004969@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
This is an interesting bug report from March that got no replies.  In my
testing, not only does CLUSTER not update the n_dead_tup statistics, but
neither does VACUUM FULL, which internally uses the CLUSTER code
(cluster_rel()).  Is this a bug?

---------------------------------------------------------------------------

Andy Lester wrote:
> 
> 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)
> 
> -- 
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

In response to

Responses

pgsql-bugs by date

Next:From: Mark LambertonDate: 2011-09-06 01:08:37
Subject: Re: BUG #6191: One click installer fails
Previous:From: Stefan KaltenbrunnerDate: 2011-09-05 18:44:40
Subject: Re: psql doesn't reuse -p after backend fail

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