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 <asif_icrossing@xxxxxxxxx> wrote:
Thanks Scott, It worked!!! We killed an old idle running transaction, now everything is fine.. Thanks Again asif ali icrossing inc Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> 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.
