pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAny way to see what queries are currently running?
6+ messages / 4 participants
[nested] [flat]
* Any way to see what queries are currently running?
@ 2005-08-15 20:54 Chris Hoover <revoohc@gmail.com>
2005-08-15 21:14 ` Re: Any way to see what queries are currently running? Guido Barosio <gbarosio@gmail.com>
2005-08-16 01:33 ` Re: Any way to see what queries are currently running? Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 04:07 ` Re: Any way to see what queries are currently running? adey <adey11@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Chris Hoover @ 2005-08-15 20:54 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Any way to see what queries are currently running?
2005-08-15 20:54 Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
@ 2005-08-15 21:14 ` Guido Barosio <gbarosio@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Guido Barosio @ 2005-08-15 21:14 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Any way to see what queries are currently running?
2005-08-15 20:54 Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
@ 2005-08-16 01:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-08-16 01:33 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Any way to see what queries are currently running?
2005-08-15 20:54 Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
@ 2005-08-16 04:07 ` adey <adey11@gmail.com>
2005-08-16 04:35 ` Re: Any way to see what queries are currently running? Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 6+ messages in thread
From: adey @ 2005-08-16 04:07 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Any way to see what queries are currently running?
2005-08-15 20:54 Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
2005-08-16 04:07 ` Re: Any way to see what queries are currently running? adey <adey11@gmail.com>
@ 2005-08-16 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 17:36 ` Re: Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-08-16 04:35 UTC (permalink / raw)
To: adey <adey11@gmail.com>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Any way to see what queries are currently running?
2005-08-15 20:54 Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
2005-08-16 04:07 ` Re: Any way to see what queries are currently running? adey <adey11@gmail.com>
2005-08-16 04:35 ` Re: Any way to see what queries are currently running? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-16 17:36 ` Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Chris Hoover @ 2005-08-16 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: adey <adey11@gmail.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-16 17:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-15 20:54 Any way to see what queries are currently running? Chris Hoover <revoohc@gmail.com>
2005-08-15 21:14 ` Guido Barosio <gbarosio@gmail.com>
2005-08-16 01:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 04:07 ` adey <adey11@gmail.com>
2005-08-16 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-16 17:36 ` Chris Hoover <revoohc@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox