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