Thread: statement_timeout
Hello. I have an unconfirmed feeling that autovac does not like system-wide statement_timeout. I.e. when I in some panic move set system-wide statement_timeout to 90 seconds, autovac stopped working (I do not know for 100% if there is a dependency). Ups... Now I checked that pg_dump has the same issue. and vacuumdb. This is all on 8.1.4 . Yeah, system-wide statement_timeout is not much of a brilliant idea :( Pozdrawiam Marcin Mańk
When I configure statement_timeout globally, I typically override it for superusers and other accounts used by dbas. Just issue: ALTER USER postgres SET statement_timeout = 0; Repeat for other superusers (slony, etc). Then the policy won't apply to them. -Casey On Nov 16, 2006, at 6:46 AM, Marcin Mańk wrote: > Hello. > I have an unconfirmed feeling that autovac does not like system-wide > statement_timeout. I.e. when I in some panic move set system-wide > statement_timeout to 90 seconds, autovac stopped working (I do not > know for > 100% if there is a dependency). > > Ups... Now I checked that pg_dump has the same issue. and vacuumdb. > This is > all on 8.1.4 . > > Yeah, system-wide statement_timeout is not much of a brilliant idea :( > > Pozdrawiam > Marcin Mańk > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/
Marcin Mañk <marcin.mank@gmail.com> writes: > Hello. > I have an unconfirmed feeling that autovac does not like system-wide > statement_timeout. I.e. when I in some panic move set system-wide > statement_timeout to 90 seconds, autovac stopped working (I do not know for > 100% if there is a dependency). > > Ups... Now I checked that pg_dump has the same issue. and vacuumdb. This is > all on 8.1.4 . > > Yeah, system-wide statement_timeout is not much of a brilliant idea :( Ok so why don't you exempt user postgres and/or any other roles known to run lengthy jobs? alter role postgres set statement_timeout to 0; > Pozdrawiam > Marcin Mañk > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant
=?iso-8859-2?Q?Marcin_Ma=F1k?= <marcin.mank@gmail.com> writes:
> I have an unconfirmed feeling that autovac does not like system-wide
> statement_timeout.
If you have it set to less than the time needed to do a vacuum, then
yes, autovac will fail. You expected differently? Do you think it's
a good idea for autovac to ignore statement_timeout? (Maybe it is,
but I suspect we'd get complaints about that too.)
regards, tom lane
> If you have it set to less than the time needed to do a vacuum, then > yes, autovac will fail. You expected differently? Do you think it's > a good idea for autovac to ignore statement_timeout? (Maybe it is, > but I suspect we'd get complaints about that too.) > "alter role set statement_timeout" solves my immediate problem (I did not know about it, thanks guys). Maybe a comment in postgresql.conf, or docs: # note: statement_timeout applies to autovacuum, pg_dump, vacuumdb etc. # If you set it globally, consider "alter role postgres set statement_timeout=0" Greetings Marcin Mańk
On Thu, 2006-11-16 at 13:50 -0500, Tom Lane wrote: > =?iso-8859-2?Q?Marcin_Ma=F1k?= <marcin.mank@gmail.com> writes: > > I have an unconfirmed feeling that autovac does not like system-wide > > statement_timeout. > > If you have it set to less than the time needed to do a vacuum, then > yes, autovac will fail. You expected differently? Do you think it's > a good idea for autovac to ignore statement_timeout? (Maybe it is, > but I suspect we'd get complaints about that too.) Autovac *must* ignore statement_timeout if it is doing a wraparound avoidance scan, surely? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Thu, 2006-11-16 at 13:50 -0500, Tom Lane wrote:
>> =?iso-8859-2?Q?Marcin_Ma=F1k?= <marcin.mank@gmail.com> writes:
>>> I have an unconfirmed feeling that autovac does not like system-wide
>>> statement_timeout.
>>
>> If you have it set to less than the time needed to do a vacuum, then
>> yes, autovac will fail. You expected differently? Do you think it's
>> a good idea for autovac to ignore statement_timeout? (Maybe it is,
>> but I suspect we'd get complaints about that too.)
> Autovac *must* ignore statement_timeout if it is doing a wraparound
> avoidance scan, surely?
Hmm. Good point. Shall we just make it ignore statement_timeout all
the time, then? We already have it overriding zero_damaged_pages ...
regards, tom lane
On Tue, 2006-11-21 at 12:14 -0500, Tom Lane wrote: > "Simon Riggs" <simon@2ndquadrant.com> writes: > > On Thu, 2006-11-16 at 13:50 -0500, Tom Lane wrote: > >> =?iso-8859-2?Q?Marcin_Ma=F1k?= <marcin.mank@gmail.com> writes: > >>> I have an unconfirmed feeling that autovac does not like system-wide > >>> statement_timeout. > >> > >> If you have it set to less than the time needed to do a vacuum, then > >> yes, autovac will fail. You expected differently? Do you think it's > >> a good idea for autovac to ignore statement_timeout? (Maybe it is, > >> but I suspect we'd get complaints about that too.) > > > Autovac *must* ignore statement_timeout if it is doing a wraparound > > avoidance scan, surely? > > Hmm. Good point. Shall we just make it ignore statement_timeout all > the time, then? We already have it overriding zero_damaged_pages ... Hmmm.... ponders a difficult choice: Having an autovacuum cancelled doesn't seem to have huge utility, but then neither does allowing a stupidly long autovacuum either. On balance if it is running, it is running for a reason, so to interrupt that reason is not useful behaviour. If anybody wants their autovacuums to run in less time they can give it more memory. So yes, autovacuum should ignore statement_timeout all of the time. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Tom, > Hmm. Good point. Shall we just make it ignore statement_timeout all > the time, then? We already have it overriding zero_damaged_pages ... +1 -- Josh Berkus PostgreSQL @ Sun San Francisco
"Simon Riggs" <simon@2ndquadrant.com> writes:
> So yes, autovacuum should ignore statement_timeout all of the time.
Actually, now that I look at it, this whole discussion was based on an
unfounded assumption. StatementTimeout is only examined upon receipt
of a client command message in postgres.c, so autovac is already not
subject to it. (The old contrib implementation would have been.)
regards, tom lane