statement_timeout

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:

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

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

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

"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