Skip site navigation (1) Skip section navigation (2)

Re: Displaying current query - eliminating <IDLE>

From: Rieback Melanie <melanie(at)cs(dot)vu(dot)nl>
To: Brad <postgresql(dot)org(at)likethesky(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Displaying current query - eliminating <IDLE>
Date: 2005-10-24 11:31:00
Message-ID: Pine.GSO.4.56.0510241307420.27624@flits.few.vu.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Thanks again for your response!

> I would imagine there'd be a fairly simple tweak that could be done to
> the source, assuming Melanie wants to build the database (and doesn't
> mind forking off from future versions
I wasn't planning on forking off new builds of Postgres.  I am actually
searching for a solution that works with existing databases.

> >Is there a way that I can manually slow down a query or transaction, to
> >ensure that I see it when I type 'SELECT current_query FROM
> >pg_stat_activity;'.  I already tried explicitly ending transactions by
> >typing:
I'm starting to think that the <IDLE> problems aren't even a reaction
time issue.  I tried using a PL/pgSQL function to deliberately slow down
the "get current query" SELECT query, to see if pg_stat_activity would
finally register it.  But that didn't work as I had hoped.

I started by creating a function:

CREATE FUNCTION delay() RETURNS VARCHAR AS '
DECLARE
    query_string VARCHAR;
BEGIN
    -- Here's where I add a delay
    for i in 1..10000 LOOP
        for j in 1..10000 LOOP
        -- I'm using two nested loops instead of one so the counter
        -- doesn't exceed the range of integers
        END LOOP;
    END LOOP;
    SELECT current_query INTO query_string FROM pg_stat_activity;
    RETURN query_string;
END;
' LANGUAGE 'plpgsql';

And then, when I typed:
SELECT current_query, delay(), current_query FROM pg_stat_activity;

at the console, the response was:
  current_query | delay  | current_query
----------------+--------+---------------
 <IDLE>         | <IDLE> | <IDLE>
 <IDLE>         | <IDLE> | <IDLE>
(2 rows)

However, when I opened up a second console, and typed:
SELECT current_query FROM pg_stat_activity;
during the noticeable delay period of the original query, it gave the
following response:

                            current_query
---------------------------------------------------------------------
 select current_query, delay(), current_query FROM pg_stat_activity;
 <IDLE>
(2 rows)

It's odd that the one console registers the currently executing query,
while the other one doesn't.  Is PostgreSQL deliberately programmed not
to not report queries that are typed into its own console?

Thanks again!
Melanie

In response to

Responses

pgsql-novice by date

Next:From: Pierre CoudercDate: 2005-10-24 13:24:48
Subject: Is there a function which eliminates spaces?
Previous:From: Benjamin PowerDate: 2005-10-24 10:30:42
Subject: Postgresql installation - Cannot add postgres user (already in use)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group