CODEWITHSUNDEEP
sqlpostgresqltimeoutpsqlpg-stat-statements
Mohan
Mohan

Reputation: 4829

pg_stat_activity keeps showing the query which has timeout due to statement_timeout

I am using the following query on the pg_stat_activity to look for the queries that are currently running:

SELECT pid, age(clock_timestamp(), query_start), usename, query, *
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' and usename='<db_user>'
ORDER BY query_start desc;

I have set a satement_timeout to 30s for this user using below query:

ALTER ROLE <db_user> set statement_timeout = 30000

I verifiy that this is set by opening a new session for db_user:

show statement_timeout;

statement_timeout|
-----------------|
30s              |

I run this query which times out after 30 seconds as expected.

SELECT pg_sleep(31);

SQL Error [57014]: ERROR: canceling statement due to statement timeout

However after this when I run the first query to see what queries are running, it shows me the timed out query as well

pid age usename query
9133 00:00:54.366638 <db_user> COMMIT
31551 00:01:49.70594 <db_user> ¶SELECT pg_sleep(31)

Why is the timeout query still showing when querying the pg_stat_activity. I expect that since this query has terminated, it must not show up in the pg_stat_activity.

Upvotes: 2

Views: 2669

Answers (2)

jjanes
jjanes

Reputation: 44137

Your monitoring query is grossly out of date. '<IDLE>' was last used as a marker for an idle query in "query" field in version 9.1. in more recent versions than that, there is a separate "state" column, which in this case will show 'idle in transaction (aborted)', while the "query" column will continue to show the most recent query, even if it is no longer running.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246093

In the query column you see the last query on that connection. This does not imply that the query is active. Check the state column of pg_stat_activity – it is probably idle, since the query has been terminated.

Upvotes: 1

Related Questions