Обсуждение: Determine current running query, but no query in pg_stat_activity
Hi, I have a process running and I am trying to work out what it is doing. Supposedly it is running a SELECT 23093 ? R 6:35 postgres: lubrook bugasbase SELECT However in pg_stat_activity, I get no current_query bugasbase=# SELECT * from pg_stat_activity ; datid | datname | procpid | usesysid | usename | current_query | query_start ----------+-----------+---------+----------+---------+---------------+------ ------- 80573819 | bugasbase | 23117 | 1 | pgsql | | 80573819 | bugasbase | 23093 | 109 | lubrook | | Shouldn't there be something in current_query? Thanks for any advice Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Adam Witney <awitney@sghms.ac.uk> writes:
> Shouldn't there be something in current_query?
Only if (a) you have stats_command_string turned on, *and* (b) you
are a superuser or the owner of the target process.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On 29/3/05 4:27 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Adam Witney <awitney@sghms.ac.uk> writes: >> Shouldn't there be something in current_query? > > Only if (a) you have stats_command_string turned on, *and* (b) you > are a superuser or the owner of the target process. I am logged in as the superuser, and I just switched stats_command_string on. Table now looks like this bugasbase=# SELECT * from pg_stat_activity ; datid | datname | procpid | usesysid | usename | current_query | query_start ----------+-----------+---------+----------+---------+---------------+------ -------------------------- 80573819 | bugasbase | 23197 | 1 | pgsql | <IDLE> | 29/03/2005 17:03:25.911288 BST 80573819 | bugasbase | 23195 | 109 | lubrook | | I did find a reference on google to the fact that there was/is a max query size length for this? I think this was for 7.3.5, is this still the case? Maybe this is the reason it is not showing up (it is quite a large query string)? Thanks Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Adam Witney <awitney@sghms.ac.uk> writes:
> I am logged in as the superuser, and I just switched stats_command_string
> on.
Just now? Too late then --- it needed to be on at the time the other
guy started his query, else he did not send the string to the stats
collector.
> I did find a reference on google to the fact that there was/is a max query
> size length for this?
Yeah, but it truncates, it doesn't just drop the string.
If you are feeling desperate you can attach to the other backend with
gdb:
$ gdb /path/to/postgres backend-PID
gdb> print debug_query_string
gdb> quit
detach and let process continue? y
$
regards, tom lane
On Tue, 2005-03-29 at 09:50, Adam Witney wrote: > On 29/3/05 4:27 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > > Adam Witney <awitney@sghms.ac.uk> writes: > >> Shouldn't there be something in current_query? > > > > Only if (a) you have stats_command_string turned on, *and* (b) you > > are a superuser or the owner of the target process. > > I am logged in as the superuser, and I just switched stats_command_string > on. Table now looks like this > > bugasbase=# SELECT * from pg_stat_activity ; > datid | datname | procpid | usesysid | usename | current_query | > query_start > ----------+-----------+---------+----------+---------+---------------+------ > -------------------------- > 80573819 | bugasbase | 23197 | 1 | pgsql | <IDLE> | > 29/03/2005 17:03:25.911288 BST > 80573819 | bugasbase | 23195 | 109 | lubrook | | > > I did find a reference on google to the fact that there was/is a max query > size length for this? I think this was for 7.3.5, is this still the case? > Maybe this is the reason it is not showing up (it is quite a large query > string)? You might want to post your reference. There hasn't been a built-in query length limit for quite some time now. MySQL's benchmark, by the way, erroneously listed postgresql as having a 16 meg max query size because (tada!) they had a 16 meg buffer allocated in their benchmark and when it filled up and errored out the size was 16 meg. I don't believe this was ever fixed, either in their code or in the report they had for a very long time on their web site. And it was pointed out again and again, by me.
Adam Witney <awitney@sghms.ac.uk> writes:
> Shouldn't there be something in current_query?
Only if (a) you have stats_command_string turned on, *and* (b) you
are a superuser or the owner of the target process.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster