Re: BUG #5932: CLUSTER doesn't update n_dead_tup - Mailing list 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@petdance.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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