Обсуждение: SET statement_timeout

We run VACUUM ANALYZE as a cron job on PostgreSQL v8.1.3 on an Opteron
box running FreeBSD 6.0-RELEASE #10. We set statement_timeout to 7200000
in postgresql.conf. Since this task often takes longer than two hours we
  encounter the following messages 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: VACUUM ANALYZE VERBOSE;

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;

I imagine that I have a silly little mistake going on but I just can't
see it. Any ideas?

Thanks ...
--
--------------------------------------------------------------------------------
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
andy.rost@noaa.gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------

andy rost <Andy.Rost@noaa.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