Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_stat_activity

pg_stat_activity

From
Bala Venkat
Date:
We are using postgres 9.0.  When I looked at the pg_stat_activity table.

I have some rows where there is difference of 2 hours between  backend_start  and xact_start

But there is only few milli seconds between xact_start and query_start. 

All them have wait as false.

My question is,  is the query still executing and also why would there be that much time difference between backend_start  and xact_start

Appreciate your thoughts and inputs..

thanks a lot..
Attachment

Re: pg_stat_activity

From
Jeff Janes
Date:
On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat <akpgeek@gmail.com> wrote:
We are using postgres 9.0.  When I looked at the pg_stat_activity table.

I have some rows where there is difference of 2 hours between  backend_start  and xact_start

But there is only few milli seconds between xact_start and query_start. 

All them have wait as false.

My question is,  is the query still executing and also why would there be that much time difference between backend_start  and xact_start

Say I connect to the database, and then I go to lunch for 2 hours.  Then I come back and start a transaction.  

Or, I connect to the database and run a two-hour query.  Then rollback/commit that, and I start another transaction.

To know the state of the query, look at the "current_query"  field.  (In newer versions, look in "state" field, but that is not in 9.0)
 
Cheers,

Jeff

Re: pg_stat_activity

From
David Johnston
Date:
Jeff Janes wrote
> On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat <

> akpgeek@

> > wrote:
>
>> We are using postgres 9.0.  When I looked at the pg_stat_activity table.
>>
>> I have some rows where there is difference of 2 hours between
>> backend_start  and xact_start
>>
>> But there is only few milli seconds between xact_start and query_start.
>>
>> All them have wait as false.
>>
>> My question is,  is the query still executing and also why would there be
>> that much time difference between backend_start  and xact_start
>>
>
> Say I connect to the database, and then I go to lunch for 2 hours.  Then I
> come back and start a transaction.
>
> Or, I connect to the database and run a two-hour query.  Then
> rollback/commit that, and I start another transaction.
>
> To know the state of the query, look at the "current_query"  field.  (In
> newer versions, look in "state" field, but that is not in 9.0)
>
> Cheers,
>
> Jeff

Or more commonly (I think at least) connection pools that keep connections
open.  Not sure but I don't think a session reset clears the back-end time
so this would be the observed behavior.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-activity-tp5798382p5798390.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.