[check_postgres] check_postgres_txn_idle returns UNKNOWN sometimes
James Skaggs
skaggs.james at gmail.com
Tue Jun 10 21:34:21 UTC 2014
It doesn't happen often, but occasionally we would see UNKNOWN coming from
the TXN_IDLE check
***** Nagios *****
Notification Type: PROBLEM
Service: PostgreSQL Txn Idle
Host: XXXXX
Address:XXXXXX
State: UNKNOWN
Date/Time: Thu May 29 12:00:49 MDT 2014
Comment:
Info:
POSTGRES_TXN_IDLE UNKNOWN: DB postgres No queries - is stats_command_string
or track_activities off?
I tried to debug this myself, but due to the transient nature of the error,
I was not successful. It seemed to me that the query might be returning no
rows, but the code seems to expect that condition. This would happen under
Postgres 9.1 and 9.2. check_postgres version 2.21.0.
Eventually, what I did was to develop a custom query that always will
return 1 row and attempts to give some good "Performance" information for
Nagios.
select coalesce(max(sec_idle), 0) as result, coalesce(max(perf), 'No idle
transactions') as perf from (
select cast (extract(epoch from current_timestamp-xact_start) as
integer) as sec_idle,
'backend_pid=' || pid || ' query=' || regexp_replace(query, E'[\r\n]',
' ', 'g') as perf
from pg_stat_activity
where state = 'idle in transaction' and current_timestamp-xact_start >
interval '10 seconds'
order by sec_idle desc limit 1
) as idlers;
Benefits:
It strips out CR and LFs to provide pretty Nagios output
It filters out some normal long-running queries
It always will return a row
Shortcomings:
pg_stat_activity.query is Postgres >= 9.2
regexp_replace is Posgres >= 8.3
Perhaps people could get some benefit from this query, or perhaps it, or
parts of it could be incorporated into check_posgres.
Thanks
James
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/check_postgres/attachments/20140610/4da16f53/attachment.html>
More information about the Check_postgres
mailing list