From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: statement_timeout doesn't work |
Date: | 2014-07-30 05:27:25 |
Message-ID: | CAL_0b1sfyJLNzfYQwW56QND_WJUDMbODtxuHX3c4JKMK2ZG0dA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jul 21, 2014 at 11:32 AM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
> On Mon, Jul 21, 2014 at 10:16 AM, David G Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>> So, If I separate the commands everything will will work as expected,
>>> correct?
>>
>> I would assume so.
>>
>> If you wait to send the DROP/ALTER index commands until the SET LOCAL
>> command returns successfully then both of those commands will die if they
>> exceed the timeout specified.
So, you were right, when I send the BEGIN/SET LOCAL/DROP/END as a
single command the statement timeout doesn't work.
Below is the test reproducing the problem.
psql -XAte <<EOF
\timing
CREATE DATABASE test;
\c test
CREATE LANGUAGE plpythonu;
EOF
psql -XAte test <<EOF
\timing
CREATE TABLE test (t text);
CREATE INDEX test_idx ON test (t);
EOF
sleep 1
psql -XAte test <<EOF &
\timing
BEGIN;
INSERT INTO test VALUES ('a');
SELECT pg_sleep(100);
END;
EOF
sleep 1
psql -XAte test -c "\
BEGIN;\
SET LOCAL statement_timeout TO 1000;\
DROP TABLE test;\
END;"
And at the separate console check the activity.
SELECT
pid, backend_start, xact_start, query_start, state_change,
waiting, state, query, now() - xact_start AS age
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid();
-[ RECORD 1 ]-+---------------------------------------------------------------
pid | 20071
backend_start | 2014-07-29 22:21:17.322722-07
xact_start | 2014-07-29 22:21:17.32666-07
query_start | 2014-07-29 22:21:17.328291-07
state_change | 2014-07-29 22:21:17.328293-07
waiting | f
state | active
query | SELECT pg_sleep(100);
age | 00:00:06.855373
-[ RECORD 2 ]-+---------------------------------------------------------------
pid | 20085
backend_start | 2014-07-29 22:21:18.330979-07
xact_start | 2014-07-29 22:21:18.332332-07
query_start | 2014-07-29 22:21:18.332332-07
state_change | 2014-07-29 22:21:18.332332-07
waiting | t
state | active
query | BEGIN;SET LOCAL statement_timeout TO 1000;DROP TABLE test;END;
age | 00:00:05.849701
The age of the compound statement is more than the specified statement timeout.
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2014-07-30 10:43:39 | String concatenation operator which keeps trailing spaces in CHAR(n) columns |
Previous Message | David G Johnston | 2014-07-30 00:38:29 | Re: Joining on CTE is unusually slow? |