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

On Sep 5, 2011, at 3:19 PM, Bruce Momjian wrote:

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?

I can't imagine how it NOT be a bug to do something that gets rid of dead tuples and then tell the user there are dead tuples when there are actually no dead tuples.

xoa

--
Andy Lester => andy@petdance.com => www.petdance.com => AIM:petdance

Andy Lester wrote:

On Sep 5, 2011, at 3:19 PM, Bruce Momjian wrote:

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?

I can't imagine how it NOT be a bug to do something that gets rid of dead tuples and then tell the user there are dead tuples when there are actually no dead tuples.

Well, if you TRUNCATE I don't think it updates the statistics either. I
think we assume the autovacuum system will analyse the new table soon.

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

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

On Sep 6, 2011, at 9:04 AM, Bruce Momjian wrote:

Well, if you TRUNCATE I don't think it updates the statistics either. I
think we assume the autovacuum system will analyse the new table soon.

When I brought this up in IRC, I recall that that sentiment was expressed, along with the standard "What do you mean you're not running autovacuum?"

--
Andy Lester => andy@petdance.com => www.petdance.com => AIM:petdance

Andy Lester wrote:

On Sep 6, 2011, at 9:04 AM, Bruce Momjian wrote:

Well, if you TRUNCATE I don't think it updates the statistics either. I
think we assume the autovacuum system will analyse the new table soon.

When I brought this up in IRC, I recall that that sentiment was
expressed, along with the standard "What do you mean you're not running
autovacuum?"

Well, if you are not running autovacuum you should be running vacuum and
analyze manually as needed.

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

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