Re: SET statement_timeout

andy rost <Andy(dot)Rost(at)noaa(dot)gov> writes:
> No big deal. We simply modified the cron job to:
> set statement_timeout=0; VACUUM ANALYZE VERBOSE;
> Should work, right?

> Now we get the following entries in our log files:

> 2006-11-30 00:03:31 CST ERROR: canceling statement due to statement timeout
> 2006-11-30 00:03:31 CST STATEMENT: set statement_timeout=0; VACUUM
> ANALYZE VERBOSE;

Hm, are you doing it like this:

psql -c "set statement_timeout=0; VACUUM ANALYZE VERBOSE;" ...

? I am not totally certain without looking at the code, but I think in
that scenario the SET would only take effect at the next command string
(which of course there won't be in a -c case). postgres.c defines a
"statement" as "whatever is sent in a single Query message", and psql -c
just crams its entire argument into a single Query --- which is unlike
psql's behavior otherwise.

You could instead do

echo "set statement_timeout=0; VACUUM ANALYZE VERBOSE;" | psql ...

in which case psql will break its input apart at semicolons, and you'll
get the behavior you expect.

BTW, you might instead consider doing

ALTER USER postgres SET statement_timeout=0

(or whatever userid you run the VACUUM as). This would make all
superuser activities immune to the timeout, which is probably a good
idea.

regards, tom lane