I'm having a problem with pg_autovacuum against both 7.3.2 and 7.4.1 on
Redhat 9 and Fedora Core 1. I'm using pg_autovacuum from 7.4.1,
everything comes from the postgresql.org RPMs.

If the number of tuples is sufficiently high, pg reports 'reltuples'
back in TABLE_STATS_QUERY in scientific notation instead of an integer.

Example:

4.35351e+06 instead of 4353514

pg_autovacuum then uses atoi() to reach the incorrect conclusion that
there are 4 tuples.  Obviously, if that table gets more than a couple of
updates, it gets vacuumed a bit too often. :)

Changing from atoi() to atof() solves the problem completely.

new_tbl->reltuples = 
  atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));

new_tbl->relpages = 
  atof(PQgetvalue(res, row, PQfnumber(res, "relpages")));

I'm not sure how I can be the only person running into this. :)



Sat, 17 Jan 2004 16:22:45 -0700
Cott Lang <cott@internetstaff.com>
Bug in pg_autovacuum ?
Cott Lang <cott@internetstaff.com> writes:
> If the number of tuples is sufficiently high, pg reports 'reltuples'
> back in TABLE_STATS_QUERY in scientific notation instead of an integer.

Right, because that column is actually a float4.

> Changing from atoi() to atof() solves the problem completely.

> new_tbl->reltuples = 
>   atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));

> new_tbl->relpages = 
>   atof(PQgetvalue(res, row, PQfnumber(res, "relpages")));

I should think this would break in different ways once reltuples exceeds
INT_MAX.  A full fix would require changing new_tbl->reltuples to be
float or double, and coping with any downstream changes that implies.

Also, relpages *is* an integer, though it's best interpreted as an
unsigned one.  (Ditto for relid.)  Looks like this code is 0-for-3 on
getting the datatypes right :-(

			regards, tom lane

Sat, 17 Jan 2004 22:18:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in pg_autovacuum ? 

Would someone review these problems and submit a patch?  Thanks.

---------------------------------------------------------------------------

Tom Lane wrote:
> Cott Lang <cott@internetstaff.com> writes:
> > If the number of tuples is sufficiently high, pg reports 'reltuples'
> > back in TABLE_STATS_QUERY in scientific notation instead of an integer.
> 
> Right, because that column is actually a float4.
> 
> > Changing from atoi() to atof() solves the problem completely.
> 
> > new_tbl->reltuples = 
> >   atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
> 
> > new_tbl->relpages = 
> >   atof(PQgetvalue(res, row, PQfnumber(res, "relpages")));
> 
> I should think this would break in different ways once reltuples exceeds
> INT_MAX.  A full fix would require changing new_tbl->reltuples to be
> float or double, and coping with any downstream changes that implies.
> 
> Also, relpages *is* an integer, though it's best interpreted as an
> unsigned one.  (Ditto for relid.)  Looks like this code is 0-for-3 on
> getting the datatypes right :-(
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 11 Feb 2004 17:29:09 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] Bug in pg_autovacuum ?
Yeah, I'll take a look at it and submit a patch.  Sorry I didn't see it
sooner, but I don't read the bugs mailing list.

On Wed, 2004-02-11 at 17:29, Bruce Momjian wrote:
> Would someone review these problems and submit a patch?  Thanks.
> 
> ---------------------------------------------------------------------------
> 
> Tom Lane wrote:
> > Cott Lang <cott@internetstaff.com> writes:
> > > If the number of tuples is sufficiently high, pg reports 'reltuples'
> > > back in TABLE_STATS_QUERY in scientific notation instead of an integer.
> > 
> > Right, because that column is actually a float4.
> > 
> > > Changing from atoi() to atof() solves the problem completely.
> > 
> > > new_tbl->reltuples = 
> > >   atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
> > 
> > > new_tbl->relpages = 
> > >   atof(PQgetvalue(res, row, PQfnumber(res, "relpages")));
> > 
> > I should think this would break in different ways once reltuples exceeds
> > INT_MAX.  A full fix would require changing new_tbl->reltuples to be
> > float or double, and coping with any downstream changes that implies.
> > 
> > Also, relpages *is* an integer, though it's best interpreted as an
> > unsigned one.  (Ditto for relid.)  Looks like this code is 0-for-3 on
> > getting the datatypes right :-(
> > 
> > 			regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> > 


Wed, 11 Feb 2004 17:57:19 -0500
"Matthew T. O'Connor" <matthew@zeut.net>
Re: [BUGS] Bug in pg_autovacuum ?
I have my original changes + Tom's recommended changes applied to 7.4.1
if you're interested.


On Wed, 2004-02-11 at 15:57, Matthew T. O'Connor wrote:
> Yeah, I'll take a look at it and submit a patch.  Sorry I didn't see it
> sooner, but I don't read the bugs mailing list.
> 
> On Wed, 2004-02-11 at 17:29, Bruce Momjian wrote:
> > Would someone review these problems and submit a patch?  Thanks.
> > 
> > ---------------------------------------------------------------------------
> > 
> > Tom Lane wrote:
> > > Cott Lang <cott@internetstaff.com> writes:
> > > > If the number of tuples is sufficiently high, pg reports 'reltuples'
> > > > back in TABLE_STATS_QUERY in scientific notation instead of an integer.
> > > 
> > > Right, because that column is actually a float4.
> > > 
> > > > Changing from atoi() to atof() solves the problem completely.
> > > 
> > > > new_tbl->reltuples = 
> > > >   atof(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
> > > 
> > > > new_tbl->relpages = 
> > > >   atof(PQgetvalue(res, row, PQfnumber(res, "relpages")));
> > > 
> > > I should think this would break in different ways once reltuples exceeds
> > > INT_MAX.  A full fix would require changing new_tbl->reltuples to be
> > > float or double, and coping with any downstream changes that implies.
> > > 
> > > Also, relpages *is* an integer, though it's best interpreted as an
> > > unsigned one.  (Ditto for relid.)  Looks like this code is 0-for-3 on
> > > getting the datatypes right :-(
> > > 
> > > 			regards, tom lane
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > > 
> 


Wed, 11 Feb 2004 16:17:37 -0700
Cott Lang <cott@internetstaff.com>
Re: [BUGS] Bug in pg_autovacuum ?
Cott Lang wrote:
> I have my original changes + Tom's recommended changes applied to 7.4.1
> if you're interested.

Sure, shoot them over to hackers or patches.  The pg_autovacuum author
is looking into this.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 11 Feb 2004 18:25:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] Bug in pg_autovacuum ?
On Wed, 2004-02-11 at 16:25, Bruce Momjian wrote:
> 
> Sure, shoot them over to hackers or patches.  The pg_autovacuum author
> is looking into this.

Here they are. They've worked well for me, but someone wiser in the ways
of C should certainly look them over. :)
Wed, 11 Feb 2004 17:28:30 -0700
Cott Lang <cott@internetstaff.com>
Re: [BUGS] Bug in pg_autovacuum ?

Not sure if anyone reported to you but we fixed these in current CVS and
will have the fix in 7.4.3.

---------------------------------------------------------------------------

Cott Lang wrote:
> On Wed, 2004-02-11 at 16:25, Bruce Momjian wrote:
> > 
> > Sure, shoot them over to hackers or patches.  The pg_autovacuum author
> > is looking into this.
> 
> Here they are. They've worked well for me, but someone wiser in the ways
> of C should certainly look them over. :)
> 
> 

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 19 Apr 2004 23:46:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] Bug in pg_autovacuum ?