Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_autovacuum reltuples bug

pg_autovacuum reltuples bug

From
"Jim C. Nasby"
Date:
There's a bug in pg_autovacuum that makes it vacuum large tables way to
frequently.

ogr=# select reltuples from pg_class where relname='ogr_summary';
  reltuples
-------------
 2.64411e+07
(1 row)

[2004-04-27 02:03:08 PM]   table name:     ogr."public"."ogr_results"
[2004-04-27 02:03:08 PM]      relid: 615097;   relisshared: 0
[2004-04-27 02:03:08 PM]      reltuples: 7;  relpages: 519951
[2004-04-27 02:03:08 PM]      curr_analyze_count:  9904180; cur_delete_count:   168282
[2004-04-27 02:03:08 PM]      ins_at_last_analyze: 9904180; del_at_last_vacuum: 168282
[2004-04-27 02:03:08 PM]      insert_threshold:    507; delete_threshold    1014
[2004-04-27 02:03:08 PM] added table: ogr."public"."ogr_results"

I suspect the issue is on line 121:
    new_tbl->reltuples = atoi(PQgetvalue(res, row, PQfnumber(res, "reltuples")));

I'm not a C coder, but doesn't atoi just take a text value and convert
it to an int? Shouldn't all the stats counters be stored as reals?
--
Jim C. Nasby, Database Consultant                  jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: pg_autovacuum reltuples bug

From
"Matthew T. O'Connor"
Date:
This is a known bug, and if fixed in CVS.  It will be released as part
of 7.4.3, or just download pg_autovacuum.c and pg_autovacuum.h from cvs
and compile a new executable.

Matthew O'Connor


Jim C. Nasby wrote:
> There's a bug in pg_autovacuum that makes it vacuum large tables way to
> frequently.
>
> ogr=# select reltuples from pg_class where relname='ogr_summary';
>   reltuples
> -------------
>  2.64411e+07
> (1 row)
>
> [2004-04-27 02:03:08 PM]   table name:     ogr."public"."ogr_results"
> [2004-04-27 02:03:08 PM]      relid: 615097;   relisshared: 0
> [2004-04-27 02:03:08 PM]      reltuples: 7;  relpages: 519951
> [2004-04-27 02:03:08 PM]      curr_analyze_count:  9904180; cur_delete_count:   168282
> [2004-04-27 02:03:08 PM]      ins_at_last_analyze: 9904180; del_at_last_vacuum: 168282
> [2004-04-27 02:03:08 PM]      insert_threshold:    507; delete_threshold    1014
> [2004-04-27 02:03:08 PM] added table: ogr."public"."ogr_results"
>
> I suspect the issue is on line 121:
>     new_tbl->reltuples = atoi(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
>
> I'm not a C coder, but doesn't atoi just take a text value and convert
> it to an int? Shouldn't all the stats counters be stored as reals?