On Mon, Jul 21, 2014 at 11:32 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> On Mon, Jul 21, 2014 at 10:16 AM, David G Johnston
> <david.g.johnston@gmail.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.ru@gmail.com
