Thread: Displaying current query - eliminating

This may come across as an odd question, but here goes..

To see the current query in PostgreSQL, the normal procedure is to type:
SELECT current_query FROM pg_stat_activity;

Now here's my question..    I want PostgreSQL to react sufficiently
quickly that the result says:

              current_query
-------------------------------------------
SELECT current_query FROM pg_stat_activity;
(1 row)

and not:

 current_query
---------------
 <IDLE>
(1 row)

In trying to achieve this behavior, I have taken the following actions:

In postgresql.conf:
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true

I also typed at the console:
\set pgstat_stat_interval 0;

This actually worked once, but since then, PostgreSQL has always reported
<IDLE>.  I've tried changing other environment variables, but with
no luck.  Nothing I've done so far has gotten it to respond consistently
in the quick-responding manner that I want.

Does anyone have any advice on how to achieve this desired behavior
consistantly?  I would be really appreciative if anyone had any tips.

Thanks!!!
Melanie R.

Rieback Melanie <melanie@cs.vu.nl> writes:
> To see the current query in PostgreSQL, the normal procedure is to type:
> SELECT current_query FROM pg_stat_activity;

> Now here's my question..    I want PostgreSQL to react sufficiently
> quickly that the result says:
> SELECT current_query FROM pg_stat_activity;

Sorry, there's basically 0 chance of that happening with any
reliability.  The stats system is designed not to slow down actual
transactions at all, which is what it would have to do to guarantee
instantaneous response.

You could improve the reaction time by reducing PGSTAT_STAT_INTERVAL
in the source code (see backend/postmaster/pgstat.c), but you'll incur
corresponding increases in overhead, and you'll never get to the point
of certain repeatability.

            regards, tom lane

Thanks for your response!

> Sorry, there's basically 0 chance of that happening with any
> reliability.  The stats system is designed not to slow down actual
> transactions at all, which is what it would have to do to guarantee
> instantaneous response.
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:

BEGIN; SELECT current_query FROM pg_stat_activity; COMMIT;

and

BEGIN; COMMIT; SELECT current_query FROM pg_stat_activity;

but that didn't seem to help.  Is there a kindof sleep command that I can
use to turn this particular query into a 'long' query, that will register
instantaneously as the current_query? (This is assuming that the database
is relatively empty, so I can't create a normal query that will take that
long).

> You could improve the reaction time by reducing PGSTAT_STAT_INTERVAL
> in the source code (see backend/postmaster/pgstat.c),
Would this have a different effect than typing:
\set pgstat_stat_interval 0
at the command prompt in the pgSQL client itself?

> but you'll incur corresponding increases in overhead, and you'll never
> get to the point of certain repeatability.
I don't care at all about increases in overhead.  I warned you that my
request is a strange one, but this is more of a puzzle, that happens to
have a real use for me.  It also doesn't have to be 100% repeatable.  50%
or even 25% repeatable would be good enough for me, as long as it works a
significant percentage of the time.

Thanks again!

Melanie

>> Sorry, there's basically 0 chance of that happening with any
>> reliability.  The stats system is designed not to slow down actual
>> transactions at all, which is what it would have to do to guarantee
>> instantaneous response.

>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:

>> but you'll incur corresponding increases in overhead, and you'll never
>> get to the point of certain repeatability.

>I don't care at all about increases in overhead.  I warned you that my
>request is a strange one, but this is more of a puzzle, that happens to
>have a real use for me.  It also doesn't have to be 100% repeatable.  50%
>or even 25% repeatable would be good enough for me, as long as it works a
>significant percentage of the time.
>
>Melanie

Tom, et al.,

Given that Postgres is an *open source* database, 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--perhaps a big if--but her request is strange
enough that maybe she wouldn't mind! ;-)...

I would imagine this would also avoid any performance hit, just a change
in the way it's done and perhaps a simple one at that (this assumes
someone familar enough with the source and who wants to help provide the
change).  It would also, presumably, give her 100% repeatability.
(Though undoubtedly 'personal' builds of postgres itself probably isn't
something that's usually covered on a 'novice' e-mail list!)

Anyway, this thread just got me 'ta thinkin' about the "open" in open
source.

Cheers,
Brad

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

On Mon, Oct 24, 2005 at 01:31:00PM +0200, Rieback Melanie wrote:
> > 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.

Could you tell us what problem you're trying to solve?  You've
said what you're trying to do but not why -- all we've seen so
far is:

> I warned you that my request is a strange one, but this is more of
> a puzzle, that happens to have a real use for me.  It also doesn't
> have to be 100% repeatable.  50% or even 25% repeatable would be
> good enough for me, as long as it works a significant percentage
> of the time.

What are you trying to do that has a real use but that can be
unreliable most of the time?

--
Michael Fuhr