Thread: Query gone wild
I'm trying to track down a query in a web application that has postgresql in a deadlock. I don't know which query is running because I only see the listing in ps aux (for now, anyway)... Is there any way to view the query that is being run from knowing a process id? Garo. =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
On Wed, 2003-01-29 at 12:58, Garo Hussenjian wrote: > I'm trying to track down a query in a web application that has postgresql in > a deadlock. By "deadlock", what exactly do you mean? > Is there any way to view the query > that is being run from knowing a process id? You can use the pg_stat_activity view in PostgreSQL 7.2 and later -- see the docs for more information: http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/monitoring-stats.html Cheers, Neil
Neil Conway <neilc@samurai.com> writes:
> On Wed, 2003-01-29 at 12:58, Garo Hussenjian wrote:
>> Is there any way to view the query
>> that is being run from knowing a process id?
> You can use the pg_stat_activity view in PostgreSQL 7.2 and later -- see
However, he probably doesn't have stats_command_string turned on, or
he'd already be aware of pg_stat_activity :-(. So this isn't much help
if the problem is to determine what an already-running query is.
Another possibility is to attach to the runaway process with a debugger
and print debug_query_string. For example, with gdb:
$ gdb /path/to/postgres-executable
gdb> attach PID-OF-PROCESS
gdb> p debug_query_string
gdb> quit
This should work unless you are running a stripped backend executable.
(On many systems, the "file" command can tell you whether an executable
has been stripped of debug symbols.)
regards, tom lane
on 1/29/03 11:11 AM, Tom Lane at tgl@sss.pgh.pa.us wrote: > Neil Conway <neilc@samurai.com> writes: >> On Wed, 2003-01-29 at 12:58, Garo Hussenjian wrote: >>> Is there any way to view the query >>> that is being run from knowing a process id? > >> You can use the pg_stat_activity view in PostgreSQL 7.2 and later -- see > > However, he probably doesn't have stats_command_string turned on, or > he'd already be aware of pg_stat_activity :-(. So this isn't much help > if the problem is to determine what an already-running query is. > > Another possibility is to attach to the runaway process with a debugger > and print debug_query_string. For example, with gdb: > > $ gdb /path/to/postgres-executable > gdb> attach PID-OF-PROCESS > gdb> p debug_query_string > gdb> quit > Thanks, Tom. Here's what I got going... semerjia% gdb usr/local/pgsql/bin/postmaster GNU gdb 4.18 < MORE GDB STUFF... > (gdb) attach 43179 Attaching to program: /usr/home/semerjia/usr/local/pgsql/bin/postmaster, process 43179 Reading symbols from /usr/lib/libz.so.2...done. Reading symbols from /usr/lib/libcrypt.so.2...done. Reading symbols from /usr/lib/libm.so.2...done. Reading symbols from /usr/lib/libutil.so.3...done. Reading symbols from /usr/lib/libreadline.so.4...done. Reading symbols from /usr/lib/libc.so.4...done. Reading symbols from /usr/lib/libncurses.so.5...done. Reading symbols from /usr/libexec/ld-elf.so.1...done. 0x28346088 in read () from /usr/lib/libc.so.4 (gdb) p debug_query_string $1 = 138514480 (gdb) > This should work unless you are running a stripped backend executable. > (On many systems, the "file" command can tell you whether an executable > has been stripped of debug symbols.) I can't tell if this worked... What is '$1 = 138514480'. After running gdb, the process STAT for 42179 changed from R to T so I kill'ed it... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
You might locate the process id with ps.... If someone has a connection on the DB -- It'll show the IP address of the user and a pid... --> ps ax | grep postgres "Garo Hussenjian" <garo@xapnet.com> wrote in message news:BA5D5658.6E5D%garo@xapnet.com... > I'm trying to track down a query in a web application that has postgresql in > a deadlock. I don't know which query is running because I only see the > listing in ps aux (for now, anyway)... Is there any way to view the query > that is being run from knowing a process id? > > Garo. > > =-=-==-=-=-== > > Xapnet Internet Solutions > 1501 Powell St., Suite N > Emeryville, CA 94608 > > Tel - (510) 655-9771 > Fax - (510) 655-9775 > Web - http://www.xapnet.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster