Re: Displaying current query - eliminating - Mailing list 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