Re: statement_timeout doesn't work

Sergey Konoplev-2 wrote
> Hi,
>
> PostgreSQL 9.2.7, Linux 2.6.32
>
> Several days ago I found one of my servers out of connections,
> pg_stat_activity showed that everything was waiting for the DROP/ALTER
> INDEX transaction (see the record 2 below), that, as I guess, was
> waiting for the function call (record 1).
>
> -[ RECORD 1 ]----------------------------------
> ts_age | 00:07:15.52997
> state | active
> query_age | 00:07:15.529945
> [...]
> waiting | f
> [...]
> query | select foo(...)
>
> -[ RECORD 2 ]----------------------------------
> ts_age | 00:06:37.844036
> state | active
> query_age | 00:06:37.844036
> [...]
> waiting | t
> [...]
> query | BEGIN;
> SET LOCAL statement_timeout TO 1000;
> DROP INDEX public.idx1;
> ALTER INDEX public.idx2 RENAME TO idx1;
> END;
>
> The interesting thing is that the transaction sets local
> statement_timeout to 1 second and its age was more than 6 minutes. The
> foo() is a complex plpython function containing all the specter of mod
> queries and using dblink(). I didn't manage to reproduce the problem
> with a simple test.

If I read this correctly you sent the entire begin...end as a single
compound statement and so, depending on how you did this, the actual SET
LOCAL command never got executed since the entire command is waiting for the
necessary locks before it can be executed.

Your sample test doesn't appear to correctly exercise this behavior. Are
you maybe using -c in the problem case? Or a client library besides psql
that would behave in this manner?

Note that the fact that "query" is a compound statement is why I claim the
above...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/statement-timeout-doesn-t-work-tp5811704p5812037.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.