Обсуждение: Checking what is the current query running

Hi all.

I've a postgresql where sometimes a process can remaing running a
query for a very long time.

Aside from logging the queries, is there a way to know which query is
running in one particular moment?

Regards
Marco

--
Marco Bizzarri
http://notenotturne.blogspot.com/

You have to enable "stats_command_string' in your postgresql.conf file:

stats_command_string = on

After that is done you can view queries running on database server like this:

select current_query from pg_stat_activity;

You can also get the query execution times and which queries were run, in the database server logs files by enabling the following in postgresql.conf file:

log_duration = on
log_statement = 'all'

Hope this helps...

Thanks,

Shoaib Mir
EnterpriseDB

Hi,

pg_stat_activity has some limitations however; if you use a lot of
dynamically generated complex queries, you might stumble over the 255
character limit of current_query in pg_stat_activity, statement-strings
longer than that are just cut off.

You can get some more information if you need it, using the GNU debugger
gdb: First you need to determine the PID of the PostgreSQL backend
that's running your query - I usually use top for that, but
pg_stat_activity does also give you that info (select procpid from
pg_stat_activity ...). Once you have the PID, you start the GNU debugger
with

gdb [path_to_postgres] [pid]
e.g. gdb /opt/pgsql/bin/postgres 551

Now issue the command
    printf "%s\n", debug_query_string
to obtain the query string. To quit the debugger, just type "quit" and
confirm - the backend will keep processing the query uninterrupted.

Kind regards

   Markus

----- Original Message -----

From: Shoaib Mir

To: Postgres

Sent: Tuesday, July 25, 2006 2:26 PM

Subject: Re: [ADMIN] Checking what is the current query running

You have to enable "stats_command_string' in your postgresql.conf file:

stats_command_string = on

After that is done you can view queries running on database server like this:

select current_query from pg_stat_activity;

You can also get the query execution times and which queries were run, in the database server logs files by enabling the following in postgresql.conf file:

log_duration = on
log_statement = 'all'

Hope this helps...

Thanks,

Shoaib Mir
EnterpriseDB

On 7/25/06, Marco Bizzarri <marco.bizzarri@gmail.com > wrote:

Hi all.

I've a postgresql where sometimes a process can remaing running a
query for a very long time.

Aside from logging the queries, is there a way to know which query is
running in one particular moment?

Regards
Marco

--
Marco Bizzarri
http://notenotturne.blogspot.com/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

"Dilipkumar" <dilipkumar_parikh@sifycorp.com> writes:
> Is there any way to log all the queries in  7.1.1 (older release).please =
> help out guys.

The very best advice anyone could give you is to stop using 7.1.anything
and get onto a more modern PG release as soon as possible.  7.1 *will*
eat your data eventually, and even in the 7.1 series, there are later
bug-fix releases than 7.1.1 ...

            regards, tom lane

"Markus Wollny" <Markus.Wollny@computec.de> writes:
> pg_stat_activity has some limitations however; if you use a lot of
> dynamically generated complex queries, you might stumble over the 255
> character limit of current_query in pg_stat_activity, statement-strings
> longer than that are just cut off.

The limit has been ~1K, not 255, for a long time (since 8.0).

            regards, tom lane