Thread: find query beening executed

find query beening executed

From
Julius Tuskenis
Date:
Hello,

I'd like to know if it is possible to find out the query beeing
processed for some connection? I know theres a "Select * from
pg_stat_activity", but if query is very long it gets "cut" and it is the
end of it that interests me.

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050



Attachment

Re: find query beening executed

From
Scott Marlowe
Date:
On Thu, Oct 22, 2009 at 1:52 AM, Julius Tuskenis <julius@nsoft.lt> wrote:
> Hello,
>
> I'd like to know if it is possible to find out the query beeing processed
> for some connection? I know theres a "Select * from pg_stat_activity", but
> if query is very long it gets "cut" and it is the end of it that interests
> me.

I usually log long running queries which gets the whole thing. (I
think...)  Logs can get big if you set the minimum too low and log a
lot.  But the query only gets logged when it's done, so you might not
have the right connection afterwards.  But you'd have the query.

Re: find query beening executed

From
Julius Tuskenis
Date:
Thank you. I'll ask to log long lasting queries, but still - isn't there
a way to see on what postgres works in real time?

2009.10.22 11:14, Scott Marlowe rašė:
> On Thu, Oct 22, 2009 at 1:52 AM, Julius Tuskenis<julius@nsoft.lt>  wrote:
>
>> Hello,
>>
>> I'd like to know if it is possible to find out the query beeing processed
>> for some connection? I know theres a "Select * from pg_stat_activity", but
>> if query is very long it gets "cut" and it is the end of it that interests
>> me.
>>
> I usually log long running queries which gets the whole thing. (I
> think...)  Logs can get big if you set the minimum too low and log a
> lot.  But the query only gets logged when it's done, so you might not
> have the right connection afterwards.  But you'd have the query.
>
>


--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050



Attachment

Re: find query beening executed

From
Scott Marlowe
Date:
On Thu, Oct 22, 2009 at 2:38 AM, Julius Tuskenis <julius@nsoft.lt> wrote:
> Thank you. I'll ask to log long lasting queries, but still - isn't there a
> way to see on what postgres works in real time?

I don't know.

You can adjust it per database btw, so if you have permissions on the
db itself, a simple

alter database smarlowe set log_min_duration_statement=1000;

Pretty sure you have to be a superuser to set that.  But no need to
even reload your db.

Re: find query beening executed

From
Jaume Sabater
Date:
On Thu, Oct 22, 2009 at 10:38 AM, Julius Tuskenis <julius@nsoft.lt> wrote:

> Thank you. I'll ask to log long lasting queries, but still - isn't there a
> way to see on what postgres works in real time?

I use pg_top for that.

http://ptop.projects.postgresql.org/

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"

Re: find query beening executed

From
Scott Marlowe
Date:
On Thu, Oct 22, 2009 at 2:50 AM, Jaume Sabater <jsabater@gmail.com> wrote:
> On Thu, Oct 22, 2009 at 10:38 AM, Julius Tuskenis <julius@nsoft.lt> wrote:
>
>> Thank you. I'll ask to log long lasting queries, but still - isn't there a
>> way to see on what postgres works in real time?
>
> I use pg_top for that.

Does it give full queries, even if they're long?  That would be really
useful sometimes.

Re: find query beening executed

From
Tom Lane
Date:
Scott Marlowe <scott.marlowe@gmail.com> writes:
> Does it give full queries, even if they're long?  That would be really
> useful sometimes.

There is no mechanism other than pg_stat_activity for seeing what
another backend is currently doing.

In recent releases you can change track_activity_query_size to set the
cutoff point for truncation of pg_stat_activity's copy of the current
query.  This is a straight tradeoff of shared memory space against
capability, and I don't recommend raising the setting to the moon.
But certainly you can make it higher than the default 1K.

            regards, tom lane