We have a view in our database.
CREATE view public.hogs AS
SELECT pg_stat_activity.procpid, pg_stat_activity.usename,
pg_stat_activity.current_query
FROM ONLY pg_stat_activity;
Select current_query from public.hogs helps us to spot errant queries
at times.
regds
mallah.
On 12/7/06, asif ali <[EMAIL PROTECTED]> wrote:
Thanks Scott,
It worked!!!
We killed an old idle running transaction, now everything is fine..
Thanks Again
asif ali
icrossing inc
Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Wed, 2006-12-06 at 15:53, asif ali wrote:
> Thanks Everybody for helping me out.
> I checked "pg_stat_activity"/pg_locks, but do not see any activity on
> the table.
> How to find a old running transaction...
> I saw this link, but it did not help..
>
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php
Sometimes just using top or ps will show you.
on linux you can run top and then hit c for show command line and look
for ones that are IDLE
Or, try ps:
ps axw|grep postgres
On my machine normally:
2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D
/home/postgres/data
2615 ? S 0:00 postgres: stats buffer process
2616 ? S 0:00 postgres: stats collector process
2857 ? S 0:00 postgres: writer process
2858 ? S 0:00 postgres: stats buffer process
2859 ? S 0:00 postgres: stats collector process
But with an idle transaction:
2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D
/home/postgres/data
2615 ? S 0:00 postgres: stats buffer process
2616 ? S 0:00 postgres: stats collector process
2857 ? S 0:00 postgres: writer process
2858 ? S 0:00 postgres: stats buffer process
2859 ? S 0:00 postgres: stats collector process
8679 ? S 0:00 postgres: smarlowe test [local] idle in transaction
Thar she blows!
Also, you can restart the database and vacuum it then too. Of course,
don't do that during regular business hours...
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
________________________________
Have a burning question? Go to Yahoo! Answers and get answers from real
people who know.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
