pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Any 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