Thread: Any way to see what queries are currently running?

Any way to see what queries are currently running?

From
Chris Hoover
Date:
I'm running 7.3.4.  Is there anyway to see the actual sql being run by
the database via a query?  I am having some problems with long running
load causing querires from our reporting system.  I have sql logging
turned on to log the sql statements to my postgres.log file.  However,
these reporting queries do not appear to be logging the entire query.
I am only getting the select clause (and I'm not sure if it's all of
it) but no from or where clause.

Can I pull these queries out from postgres?

Thanks,

Chris

Re: Any way to see what queries are currently running?

From
Guido Barosio
Date:
In order to know about the current activity you should
 
"select * from pg_stat_activity" which I think is a view.
 
You may also want to \d pg_stat_activity to check the relations you may find
usefull.
 
But, if this is a default installation, you may find that you need to tune some parameters
in your postgresql.conf file. Read the man pages (online documentation) in order to understand
better what you are looking for.
 
Best wishes,
G.-

 
On 8/15/05, Chris Hoover <revoohc@gmail.com> wrote:
I'm running 7.3.4.  Is there anyway to see the actual sql being run by
the database via a query?  I am having some problems with long running
load causing querires from our reporting system.  I have sql logging
turned on to log the sql statements to my postgres.log file.  However,
these reporting queries do not appear to be logging the entire query.
I am only getting the select clause (and I'm not sure if it's all of
it) but no from or where clause.

Can I pull these queries out from postgres?

Thanks,

Chris

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings



--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.

Re: Any way to see what queries are currently running?

From
Tom Lane
Date:
Chris Hoover <revoohc@gmail.com> writes:
> I'm running 7.3.4.  Is there anyway to see the actual sql being run by
> the database via a query?  I am having some problems with long running
> load causing querires from our reporting system.  I have sql logging
> turned on to log the sql statements to my postgres.log file.  However,
> these reporting queries do not appear to be logging the entire query.
> I am only getting the select clause (and I'm not sure if it's all of
> it) but no from or where clause.

That seems exceedingly improbable.  Are you sure you are reading the log
correctly?  If the source queries are formatted as multiple lines, the
log output will be broken across lines too ...

            regards, tom lane

Re: Any way to see what queries are currently running?

From
adey
Date:
You could try the following query after enabling the
stats_command_string parameter in the postgresql.conf file:-

-- displays current activity in the database
-- postgresql.conf parameter stats_command_string populates the
current_query -- column


select     datid,

    datname as "DB Name",

    substr(procpid,1,6) as "Procpid",

    substr(usesysid,1,5),

    usename,

    current_query as SQL,

    query_start

from

    pg_stat_activity

order by

    procpid

On 8/16/05, Chris Hoover <revoohc@gmail.com> wrote:
> I'm running 7.3.4.  Is there anyway to see the actual sql being run by
> the database via a query?  I am having some problems with long running
> load causing querires from our reporting system.  I have sql logging
> turned on to log the sql statements to my postgres.log file.  However,
> these reporting queries do not appear to be logging the entire query.
> I am only getting the select clause (and I'm not sure if it's all of
> it) but no from or where clause.
>
> Can I pull these queries out from postgres?
>
> Thanks,
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: Any way to see what queries are currently running?

From
Tom Lane
Date:
adey <adey11@gmail.com> writes:
> You could try the following query after enabling the
> stats_command_string parameter in the postgresql.conf file:-

The pg_stat_activity mechanism *does* have a limit on the query
string length it can display, and if memory serves that limit was
not large in 7.3.* --- I'm thinking 256 bytes.

However, the postmaster log doesn't have any hard limit that I'm
aware of, so it seems like Chris' original approach should work.

            regards, tom lane

Re: Any way to see what queries are currently running?

From
Chris Hoover
Date:
Ok, I was able to log the postgres.log and I found the entire query.
I am very sorry for causing this confusion.  The way the query was
being submitted made it look like it was being cut off, and our daily
log files are multi-gig so mining them is quite a pain. (Also, I was
quite under the weather yesterday).

Anyway, it good to know there are some possible options besides always
mining the logs.

Thanks,

Chris

On 8/16/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> adey <adey11@gmail.com> writes:
> > You could try the following query after enabling the
> > stats_command_string parameter in the postgresql.conf file:-
>
> The pg_stat_activity mechanism *does* have a limit on the query
> string length it can display, and if memory serves that limit was
> not large in 7.3.* --- I'm thinking 256 bytes.
>
> However, the postmaster log doesn't have any hard limit that I'm
> aware of, so it seems like Chris' original approach should work.
>
>                         regards, tom lane
>