Lists: | pgsql-bugspgsql-hackerspgsql-patches |
---|
From: | "Galy Lee" <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-05-31 09:14:06 |
Message-ID: | 200705310914.l4V9E6JA094603@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
The following bug has been logged online:
Bug reference: 3326
Logged by: Galy Lee
Email address: lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp
PostgreSQL version: 8.3
Operating system: Red Hat 4.
Description: Invalid lower bound of autovacuum_cost_limit
Details:
Hello
I found some bugs which relative to the autovacuum_cost_limit GUC
parameter.
* Bug-1: Invalid lower bound of autovacuum_cost_limit
autovacuum_vacuum_cost_limit should be the following value:
autovacuum_vacuum_cost_limit = -1, or [1, 10000]
(0 should be prohibited. )
But 0 can also be accepted for autovacuum_vacuum_cost_limit now.
This causes zero-division error for autovacuum:
ERROR: floating-point exception
DETAIL: An invalid floating-point operation was signaled. This
probably means an out-of-range result or an invalid operation,
such as division by zero.
* Bug-2: 0-cost-limit for autovacuum worker
When autovacuum_max_workers > autovacuum_vacuum_cost_limit, the above
zero-division error also happened.
* Bug-3: no GUC constrain check for pg_autovacuum
The settings in pg_autovacuum are not checked enough now. Invalid value
can be passed to autovacuum, this also causes some columns in
pg_autovacuum has inconsistent upper and lower bound with their original
GUC constrain.
pg_autovacuum colum | definition | GUC constrain
--------------------+------------+--------------------------
vac_base_thresh | integer | [0, INT_MAX]
vac_scale_factor | real | [0.0, 100.0]
anl_base_thresh | integer | [0, INT_MAX]
anl_scale_factor | real | [0.0, 100.0]
vac_cost_delay | integer | [-1, 1000]
vac_cost_limit | integer | [-1, 10000]
freeze_min_age | integer | [0, 1000000000]
freeze_max_age | integer | [100000000, 2000000000]
The above table shows the wrong mapping between pg_autovacuum columns
and GUC constrain.
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-07 01:56:45 |
Message-ID: | 20070607015645.GC30257@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Galy Lee wrote:
Hi Galy,
> The following bug has been logged online:
>
> Bug reference: 3326
> Logged by: Galy Lee
> Email address: lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp
> PostgreSQL version: 8.3
> Operating system: Red Hat 4.
> Description: Invalid lower bound of autovacuum_cost_limit
> Details:
Damn. Thanks for reporting -- I'll investigate these issues.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp> |
Cc: | pgsql-patches(at)postgresql(dot)org |
Subject: | Re: [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-07 15:53:36 |
Message-ID: | 20070607155336.GM3664@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Galy Lee wrote:
Hi,
I'll deal with each issue separately.
> * Bug-1: Invalid lower bound of autovacuum_cost_limit
>
> autovacuum_vacuum_cost_limit should be the following value:
> autovacuum_vacuum_cost_limit = -1, or [1, 10000]
> (0 should be prohibited. )
>
> But 0 can also be accepted for autovacuum_vacuum_cost_limit now.
This is solved easily by adding a check in an assign hook (attached).
The only remaining problem here is that other error messages could be
clearer.
So this is correct:
$ postmaster -c autovacuum_vacuum_cost_limit=0
17902 FATAL: invalid value for parameter "autovacuum_vacuum_cost_limit": 0
But this is misleading (started postmaster with good value, then edited
postgresql.conf and entered "-2"):
17903 LOG: received SIGHUP, reloading configuration files
17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)
Note how it still says the range is -1 .. 1000.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachment | Content-Type | Size |
---|---|---|
autovac-cost-limit-2.patch | text/x-diff | 1.4 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org |
Subject: | Re: [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-07 16:18:44 |
Message-ID: | 12511.1181233124@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> But this is misleading (started postmaster with good value, then edited
> postgresql.conf and entered "-2"):
> 17903 LOG: received SIGHUP, reloading configuration files
> 17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)
> Note how it still says the range is -1 .. 1000.
Can we redefine things to make zero be the "disabled" value, thus
keeping the range of valid values contiguous?
regards, tom lane
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-07 16:54:03 |
Message-ID: | 20070607165403.GS3664@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > But this is misleading (started postmaster with good value, then edited
> > postgresql.conf and entered "-2"):
>
> > 17903 LOG: received SIGHUP, reloading configuration files
> > 17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)
>
> > Note how it still says the range is -1 .. 1000.
>
> Can we redefine things to make zero be the "disabled" value, thus
> keeping the range of valid values contiguous?
That would be another solution ... though it would be different from the
valid value for autovacuum_vacuum_cost_delay (on which 0 is a valid
value). Also it would be a different value from previous versions.
I don't think either of these is a showstopper, so let's go for that if
nobody objects.
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"
From: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-07 17:09:44 |
Message-ID: | 46683BD8.9080707@zeut.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Alvaro Herrera wrote:
> Tom Lane wrote:
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> But this is misleading (started postmaster with good value, then edited
>>> postgresql.conf and entered "-2"):
>>> 17903 LOG: received SIGHUP, reloading configuration files
>>> 17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)
>>> Note how it still says the range is -1 .. 1000.
>> Can we redefine things to make zero be the "disabled" value, thus
>> keeping the range of valid values contiguous?
>
> That would be another solution ... though it would be different from the
> valid value for autovacuum_vacuum_cost_delay (on which 0 is a valid
> value). Also it would be a different value from previous versions.
>
> I don't think either of these is a showstopper, so let's go for that if
> nobody objects.
Can you make 0 and -1 both valid disabled values? That way it will be
compatible with previous releases.
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-07 19:02:01 |
Message-ID: | 20070607190201.GE21004@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Tom Lane wrote:
> >>Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> >>>But this is misleading (started postmaster with good value, then edited
> >>>postgresql.conf and entered "-2"):
> >>>17903 LOG: received SIGHUP, reloading configuration files
> >>>17903 LOG: -2 is outside the valid range for parameter
> >>>"autovacuum_vacuum_cost_limit" (-1 .. 1000)
> >>>Note how it still says the range is -1 .. 1000.
> >>Can we redefine things to make zero be the "disabled" value, thus
> >>keeping the range of valid values contiguous?
> >
> >That would be another solution ... though it would be different from the
> >valid value for autovacuum_vacuum_cost_delay (on which 0 is a valid
> >value). Also it would be a different value from previous versions.
> >
> >I don't think either of these is a showstopper, so let's go for that if
> >nobody objects.
>
> Can you make 0 and -1 both valid disabled values? That way it will be
> compatible with previous releases.
Heh, sure, we can do that too and it doesn't seem like anybody would
object. I will patch the documentation so that that the "disabled"
value is zero, and still allow -1. That way it doesn't seem like there
should be any objection.
--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-08 14:41:42 |
Message-ID: | 20070608144142.GG9071@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
> > Can you make 0 and -1 both valid disabled values? That way it will be
> > compatible with previous releases.
>
> Heh, sure, we can do that too and it doesn't seem like anybody would
> object. I will patch the documentation so that that the "disabled"
> value is zero, and still allow -1. That way it doesn't seem like there
> should be any objection.
Patch attached.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)
Attachment | Content-Type | Size |
---|---|---|
autovac-zero-cost-limit.patch | text/x-diff | 6.9 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-08 14:54:26 |
Message-ID: | 25462.1181314466@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Matthew T. O'Connor wrote:
>>> Can you make 0 and -1 both valid disabled values? That way it will be
>>> compatible with previous releases.
>>
>> Heh, sure, we can do that too and it doesn't seem like anybody would
>> object. I will patch the documentation so that that the "disabled"
>> value is zero, and still allow -1. That way it doesn't seem like there
>> should be any objection.
> Patch attached.
It seems like documenting vac_cost_limit as being different from the
others will just create perceived complexity/confusion, with no real
benefit. I'd suggest leaving the documentation and the default value
alone, and applying just the part of the patch that causes 0 to be
silently treated as if it were -1.
A comment at the spot where this is done would be a good idea, but
I don't think we need to say anything in the SGML docs.
regards, tom lane
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Galy Lee <lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3326: Invalid lower bound of autovacuum_cost_limit |
Date: | 2007-06-08 15:00:50 |
Message-ID: | 20070608150050.GH9071@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers pgsql-patches |
Galy Lee wrote:
> * Bug-2: 0-cost-limit for autovacuum worker
>
> When autovacuum_max_workers > autovacuum_vacuum_cost_limit, the above
> zero-division error also happened.
Ah, this is a problem in the balance code -- it fails to consider that
the cost limit may be end up being 0 in the integer calculations. This
patch fixes this problem.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Attachment | Content-Type | Size |
---|---|---|
autovac-balance-limit.patch | text/x-diff | 1.2 KB |