Query gone wild

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

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

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