Re: pg_catalog.pg_stat_activity and current_query

On 1 March 2011 09:36, Alex <alexadri83(at)virgilio(dot)it> wrote:

> Good morning,
>
> I'm trying to make a Postgre profiler reading
> pg_catalog.pg_stat_activity.
> But when I read that view... I always find my query.
>
> Example:
>
> ResultSet rs = st.executeQuery("SELECT query_start,current_query FROM
> pg_catalog.pg_stat_activity where xact_start is not null");
> while(rs.next()) {
> String sQuery = rs.getString("current_query");
> qStart = rs.getTimestamp("query_start");
> if(!qStart.equals(qStart_last)){
> display.append(sQuery+'\n');
> qStart_last=(Timestamp)qStart.clone();
> }
> }
>
> Always prints "SELECT query_start,current_query FROM
> pg_catalog.pg_stat_activity where xact_start is not null" :)
>
> I'd like to discard my query... any ideas?
>
> Thanks
>
>
If you want just to get rid of you query, add this to your query:

and procpid <> pg_backend_pid()

so you will have something like this:

SELECT query_start,current_query FROM
pg_catalog.pg_stat_activity where xact_start is not null and procpid <>
pg_backend_pid()

regards
Szymon