find query beening executed

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

On Thu, Oct 22, 2009 at 1:52 AM, Julius Tuskenis <julius(at)nsoft(dot)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.

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(at)nsoft(dot)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

On Thu, Oct 22, 2009 at 2:38 AM, Julius Tuskenis <julius(at)nsoft(dot)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.

On Thu, Oct 22, 2009 at 10:38 AM, Julius Tuskenis <julius(at)nsoft(dot)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"

On Thu, Oct 22, 2009 at 2:50 AM, Jaume Sabater <jsabater(at)gmail(dot)com> wrote:
> On Thu, Oct 22, 2009 at 10:38 AM, Julius Tuskenis <julius(at)nsoft(dot)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.

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)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