Re: how to determine what a process is doing

Tom Lane wrote:
> Alex Howansky <alex@wankwood.com> writes:
> > Is there any way to determine exactly what a postgres process is
> > doing at any time? The output from the ps command only shows "INSERT" or
> > "SELECT", and not the full query string.
>
> There isn't any really nice solution at the moment, but you could run
> the postmaster with -d2 to cause writing of all queries to the
> postmaster's log file (ie, its stdout/stderr).  You'd probably also want
> to compile with ELOG_TIMESTAMPS defined (see include/config.h) to get
> timestamps and process PIDs included in the log.  That'd give you info
> to correlate against what "top" shows.

    If  you  compiled  postgres with -g (what I do by default :-)
    you could use this little script:

        #!/bin/sh

        gdb <<_EOF_
        file /usr/home/pgsql/bin/postgres
        attach $1
        break pg_exec_query_string
        commands 1
        silent
        print query_string
        continue
        end
        continue
        _EOF_

    OK, could have some error checking and so, but it's  a  quick
    hack - not a final solution.

    Find  the PID of a backend you want to examine and give it as
    argument to the script. It'll then attach to the backend  and
    dump all queries sent from PHP until you hit ^C. It'll detach
    again and the PHP script will never know.

    If you redirect it's output  to  a  file,  just  wait  a  few
    seconds and hit ^C, there will not even be much delay for the
    PHP. So the user might not notice too.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #