pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLong running transaction in pg_activity_log
4+ messages / 3 participants
[nested] [flat]
* Long running transaction in pg_activity_log
@ 2007-08-22 20:40 Sachchida Ojha <sojha@secure-elements.com>
2007-08-22 20:54 ` Re: Long running transaction in pg_activity_log Sachchida Ojha <sojha@secure-elements.com>
2007-08-22 21:08 ` Re: Long running transaction in pg_activity_log Tobias Brox <tobias@nordicbet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Sachchida Ojha @ 2007-08-22 20:40 UTC (permalink / raw)
To: pgsql-performance
I see some long running transaction in my pg_activity_log table. My app
becomes almost unusable. My question is
How can I query the database to see what sql these transactions are
running.
16385;"em_db";20893;16386;"em_user";"<IDLE> in
transaction";f;"2007-08-22 20:38:06.527792+00";"2007-08-22
20:37:33.937193+00";"127.0.0.1";52466
16385;"em_db";15110;16386;"em_user";"<IDLE> in
transaction";f;"2007-08-22 20:05:08.580643+00";"2007-08-22
19:39:11.670572+00";"127.0.0.1";50961
16385;"em_db";15113;16386;"em_user";"<IDLE> in
transaction";f;"2007-08-22 20:06:01.53394+00";"2007-08-22
19:39:11.704564+00";"127.0.0.1";50964
Thanks
Regards
Sachi
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Long running transaction in pg_activity_log
2007-08-22 20:40 Long running transaction in pg_activity_log Sachchida Ojha <sojha@secure-elements.com>
@ 2007-08-22 20:54 ` Sachchida Ojha <sojha@secure-elements.com>
1 sibling, 0 replies; 4+ messages in thread
From: Sachchida Ojha @ 2007-08-22 20:54 UTC (permalink / raw)
To: Sachchida Ojha <sojha@secure-elements.com>; pgsql-performance
I can see SQL in the current SQL column of pg_activity_log when I run
sql from sql window or throgh odbc/jdbc connection but when sql is
embedded in java beaans i can see those sql. Is there any way to
extract those sql from the database.
16385;"em_db";20220;16388;"emsowner";"select * from
pg_stat_activity;";f;"2007-08-22 20:48:25.062663+00";"2007-08-22
20:30:58.600489+00";"10.0.200.165";4170
Regards
Sachchida
________________________________
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Sachchida
Ojha
Sent: Wednesday, August 22, 2007 4:40 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Long running transaction in pg_activity_log
I see some long running transaction in my pg_activity_log table. My app
becomes almost unusable. My question is
How can I query the database to see what sql these transactions are
running.
16385;"em_db";20893;16386;"em_user";"<IDLE> in
transaction";f;"2007-08-22 20:38:06.527792+00";"2007-08-22
20:37:33.937193+00";"127.0.0.1";52466
16385;"em_db";15110;16386;"em_user";"<IDLE> in
transaction";f;"2007-08-22 20:05:08.580643+00";"2007-08-22
19:39:11.670572+00";"127.0.0.1";50961
16385;"em_db";15113;16386;"em_user";"<IDLE> in
transaction";f;"2007-08-22 20:06:01.53394+00";"2007-08-22
19:39:11.704564+00";"127.0.0.1";50964
Thanks
Regards
Sachi
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Long running transaction in pg_activity_log
2007-08-22 20:40 Long running transaction in pg_activity_log Sachchida Ojha <sojha@secure-elements.com>
@ 2007-08-22 21:08 ` Tobias Brox <tobias@nordicbet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tobias Brox @ 2007-08-22 21:08 UTC (permalink / raw)
To: Sachchida Ojha <sojha@secure-elements.com>; +Cc: pgsql-performance
[Sachchida Ojha - Wed at 04:40:09PM -0400]
> I see some long running transaction in my pg_activity_log table. My app
> becomes almost unusable. My question is
> How can I query the database to see what sql these transactions are
> running.
"<IDLE> in transaction" means that no sql query is running at the
moment.
Most probably it's a problem with the application, it starts a
transaction, but does not close it (through a commit or rollback). This
is very harmful for the performance, as the hours, days and weeks pass
by any database supporting transactions will get problems.
Restarting the application and vacuuming is a one-time-shot which should
solve the problem for a short while. For a permanent fix, the
application needs to be fixed, or you'll have to ensure that the
autocommit feature is used.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Long running transaction in pg_activity_log
@ 2007-08-22 21:58 Farhan Mughal <farhanhafeezmughal@yahoo.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Farhan Mughal @ 2007-08-22 21:58 UTC (permalink / raw)
To: Sachchida Ojha <sojha@secure-elements.com>; pgsql-performance
If I am not wrong, you must be asking about this:
select * from pg_stat_activity;
If you see <command string
not enabled> in the query column.
Turn on the stats_command_string option in postgresql.conf.
Regards,
Farhan
----- Original Message ----
From: Sachchida Ojha <sojha@secure-elements.com>
To: pgsql-performance@postgresql.org
Sent: Thursday, 23 August, 2007 1:40:09 AM
Subject: [PERFORM] Long running transaction in pg_activity_log
Long running transaction in pg_activity_log
I see some long running transaction in my pg_activity_log table. My app becomes almost unusable. My question is
How can I query the database to see what sql these transactions are running.
16385;"em_db";20893;16386;"em_user";"<IDLE> in transaction";f;"2007-08-22 20:38:06.527792+00";"2007-08-22 20:37:33.937193+00";"127.0.0.1";52466
16385;"em_db";15110;16386;"em_user";"<IDLE> in transaction";f;"2007-08-22 20:05:08.580643+00";"2007-08-22 19:39:11.670572+00";"127.0.0.1";50961
16385;"em_db";15113;16386;"em_user";"<IDLE> in transaction";f;"2007-08-22 20:06:01.53394+00";"2007-08-22 19:39:11.704564+00";"127.0.0.1";50964
Thanks
Regards
Sachi
___________________________________________________________
Want ideas for reducing your carbon footprint? Visit Yahoo! For Good http://uk.promotions.yahoo.com/forgood/environment.html
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-22 21:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-22 20:40 Long running transaction in pg_activity_log Sachchida Ojha <sojha@secure-elements.com>
2007-08-22 20:54 ` Sachchida Ojha <sojha@secure-elements.com>
2007-08-22 21:08 ` Tobias Brox <tobias@nordicbet.com>
2007-08-22 21:58 Re: Long running transaction in pg_activity_log Farhan Mughal <farhanhafeezmughal@yahoo.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox