Обсуждение: Any way to see what queries are currently running?

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

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

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
>

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

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
>