Re: statement_timeout doesn't work

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