[check_postgres] check_postgres_txn_idle returns UNKNOWN sometimes

[James Skaggs]

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>