Re: [GENERAL] set statement_timeout does not work
Hi, I try to set the statement_timeout so that select pg_stop_backup(); will not hang if archive command failed. Below are the command and Can somebody help on this? Thanks. * From: Jun Wang junw2...@gmail.com * To: pgsql-general@postgresql.org * Subject: set statement_timeout does not work * Date: Mon, 29 Mar 2010 19:33:55 -0700 * Message-id: deff9e831003291933k63585027h5afcc1451f91d...@mail.gmail.com Hi, I try to set the statement_timeout so that select pg_stop_backup(); will not hang if archive command failed. Below are the command and errors. psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c set statement_timeout = 1000; select pg_stop_backup(); WARNING: pg_stop_backup still waiting for archive to complete (60 seconds elapsed) WARNING: pg_stop_backup still waiting for archive to complete (120 seconds elapsed) WARNING: pg_stop_backup still waiting for archive to complete (240 seconds elapsed) WARNING: pg_stop_backup still waiting for archive to complete (480 seconds elapsed) I also tried to run the two commands seperately as below. It also does not work. psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c set statement_timeout = 1000; psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c select pg_stop_backup(); If I change the statement_timeout setting of postgresql.conf, it works. But it will afftect all the queries. How to use psql to do it? Thanks. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] set statement_timeout does not work
On Mon, Mar 29, 2010 at 8:33 PM, Jun Wang junw2...@gmail.com wrote: Hi, I try to set the statement_timeout so that select pg_stop_backup(); will not hang if archive command failed. Below are the command and errors. Try it by putting the commands in a file and running it like psql . -f mysqlfile.sql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] set statement_timeout does not work
Hi, I try to set the statement_timeout so that select pg_stop_backup(); will not hang if archive command failed. Below are the command and errors. psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c set statement_timeout = 1000; select pg_stop_backup(); WARNING: pg_stop_backup still waiting for archive to complete (60 seconds elapsed) WARNING: pg_stop_backup still waiting for archive to complete (120 seconds elapsed) WARNING: pg_stop_backup still waiting for archive to complete (240 seconds elapsed) WARNING: pg_stop_backup still waiting for archive to complete (480 seconds elapsed) I also tried to run the two commands seperately as below. It also does not work. psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c set statement_timeout = 1000; psql.exe -d mydb -h myhost -p 5432 -U postgres -w -c select pg_stop_backup(); If I change the statement_timeout setting of postgresql.conf, it works. But it will afftect all the queries. How to use psql to do it? Thanks. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SET statement_timeout problem
I have a problem with fetching from cursors sometimes taking an extremely long time to run. I am attempting to use the statement_timeout parameter to limit the runtime on these. PostgreSQL 8.2.4 Linux 2.6.22.14-72.fc6 #1 SMP Wed Nov 21 13:44:07 EST 2007 i686 i686 i386 GNU/Linux begin; set search_path = testdb; declare cur_rep cursor for select * from accounts, individual; set statement_timeout = 1000; fetch forward 100 from cur_rep; The open join, 1000ms, and 100 count are all intentional. Normally those values would be 30 and 1. The accounts and individual tables have around 100 fields and 500k records each. Nested Loop (cost=21992.28..8137785497.71 rows=347496704100 width=8) - Seq Scan on accounts (cost=0.00..30447.44 rows=623844 width=8) - Materialize (cost=21992.28..29466.53 rows=557025 width=0) - Seq Scan on individual (cost=0.00..19531.25 rows=557025 width=0) I tried moving the SET statment before the cursor delcaration and outside the transaction with the same results. I thought possibly it was getting bogged down in I/O but the timeout seems to work fine if not using a cursor. What am I missing here? Thanks, Joe _ The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you. _ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 720 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 [EMAIL PROTECTED] http://www.nohrsc.noaa.gov ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SET statement_timeout
andy rost [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
