Re: pg_stat_get_backen_last_activity() ???

> > Is there any notion of pg_stat_get_backend_last_activity()? I've
> > got a rogue process someplace and I'm having a devil of a time
> > tracking down which process is not committing its transaction and
> > letting the rest of the world continue to access the tables.
>
> Perhaps look in pg_locks to see who's holding the oldest open
> transaction number, or who's got the most locks that other people
> are waiting for? That should get you at least the PID of the
> offender. Then use ps and/or pg_stat_activity to figure out what
> the offender is doing.

I can already identify that. Using the PID of the backend + sockstat
on both the DB server and on the remote www server I can get the PID
of the webserver process causing the problem, but I have no clue which
customer, which page, which query, etc. The www process is doing
something along the lines of:

BEGIN;
SELECT....
[waits forever]
END;

I could easily grep and figure out what the query is if I knew what
the prior query was because that'd give me some indication as to which
page it was that was sitting there pouting inside of a transaction.
Attaching GDB to random httpd processes isn't my idea of fun. :-/ The
other problem is that with the frequency being around once every week
or so, it's one of those largely annoying things that isn't too
critical in its frequency, but just critical enough to spend some
cycles on here and there. -sc

--
Sean Chittenden