Thread: BUG #5932: CLUSTER doesn't update n_dead_tup
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)
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: >=20 > 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 t= uples and then tell the user there are dead tuples when there are actually = no dead tuples. xoa =20 -- Andy Lester =3D> andy@petdance.com =3D> www.petdance.com =3D> 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 deadtuples 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?"= =20=20 -- Andy Lester =3D> andy@petdance.com =3D> www.petdance.com =3D> 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. +