Обсуждение: Query to detect long-running transactions?

I'm wondering if there is a query I could run to detect long-running
transactions?  Basically, I want to know all transactions (either on a
given server or just on a given database) that have been running for
longer than some parameterized amount of time.

I've spent some time with the friendly manual (especially Chapter 42 and
12), and didn't see anything usefull.

I'm having a problem with stupid humans (including yours truely)
forgetting they have a transaction outstanding and wandering off for a
weekend.  What I'd like to do is write a quick script that queries the
database every so often, and if it finds a transaction that's been
running to long, send out an email warning people.  I might be able to
do this by grovelling through the logs, although it'd be much easier to
do it with a query.

Thanks.

Brian

am  Mon, dem 22.10.2007, um 10:46:58 -0400 mailte Brian Hurt folgendes:
>
> I'm wondering if there is a query I could run to detect long-running
> transactions?  Basically, I want to know all transactions (either on a
> given server or just on a given database) that have been running for
> longer than some parameterized amount of time.

You can log all statements with a duration time higher than a limit:

log_min_duration_statement = N

with N in ms.

>
> I've spent some time with the friendly manual (especially Chapter 42 and
> 12), and didn't see anything usefull.
>
> I'm having a problem with stupid humans (including yours truely)
> forgetting they have a transaction outstanding and wandering off for a
> weekend.  What I'd like to do is write a quick script that queries the

You can query pg_stat_activity for query_start and compare that with the
current time to find long running transactions (current_query : '<IDLE>
in transaction'). Hope that helps.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net