Bug in pg_autovacuum ?
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. :)
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
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
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.
Show quoted text
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
I have my original changes + Tom's recommended changes applied to 7.4.1
if you're interested.
Show quoted text
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
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 19073On 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. :)
Attachments:
autovac.patchtext/x-patch; charset=; name=autovac.patchDownload+9-7
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?
--
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