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

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. +