Re: pg_stat_get_backen_last_activity() ???

> > > Any ideas on how I can figure out what the mystery query/app/lib
> > > is that's causing my backend to hang with a zillion connections
> > > that are all idle?
> >
> > Well, that particular backend is not hung: the stack trace proves it's
> > waiting for a command from its frontend. Perhaps you need to look
> > through your other Postgres processes.
>
> Sorry, hang is the wrong word. Block is more correct. I know it's
> waiting for commands from the front end, but I'm trying to figure out
> what front end/query/lib/page is causing the problem. It's only once
> in few thousand queries that it happens and I'm going crazy trying to
> figure out what it is that's causing this. I've set
> stats_command_string to true and attached gdb to the backend, but
> that's null. Is there another var in the backend that contains a
> pointer to the last query performed? I haven't found it if there
> is... -sc

Hrm, guess this'll work:

log_pid = true
log_statement = true
log_duration = true
log_timestamp = true

Lower tech, but seems to work well effective. I changed pg_ctl to zip
my error logs though, otherwise it grows too quickly. :-/

- "$po_path" ${1+"$@"} ${PGDATAOPTS+$PGDATAOPTS} </dev/null >>$logfile 2>& 1 &
+ "$po_path" ${1+"$@"} ${PGDATAOPTS+$PGDATAOPTS} </dev/null | $gzip -${ziplevel} -c >>$logfile 2>& 1 &

-sc

--
Sean Chittenden