Обсуждение: How to list what queries are running in postgres?

Hi!

I'm looking for a command or method to find out what queries are
currently being serviced by the database.

Once in a while a query goes berzerk and uses up all CPU. I'd like to
find out which query is the culprit and want to kill it off. In Sybase
you can get a list of running "processes" within the database and kill
the offending one. Is there an equivalent in postgres?

Right now we only have the method to shutdown the database en restart it
to get rid of the culprit.

Thanks in advance!

Best regards,

    Pascal

P.A.M. van Dam wrote:

> Hi!
>
> I'm looking for a command or method to find out what queries are
> currently being serviced by the database.

select * from pg_stat_activity;


Regards
Gaetano Mendola

Gaetano Mendola <mendola@bigfoot.com> writes:
> P.A.M. van Dam wrote:
>> I'm looking for a command or method to find out what queries are
>> currently being serviced by the database.

> select * from pg_stat_activity;

... having first remembered to turn on stats_command_string; and
thou shalt also be superuser.

[ anybody else remember Monty Python's scriptures concerning the
Holy Hand Grenade? ]

            regards, tom lane

Tom Lane wrote:

> Gaetano Mendola <mendola@bigfoot.com> writes:
>
>>P.A.M. van Dam wrote:
>>
>>>I'm looking for a command or method to find out what queries are
>>>currently being serviced by the database.
>
>
>>select * from pg_stat_activity;
>
>
> ... having first remembered to turn on stats_command_string; and
> thou shalt also be superuser.
>
> [ anybody else remember Monty Python's scriptures concerning the
> Holy Hand Grenade? ]

Do you mean this ?

Then did he raise on high the Holy Hand Grenade of Antioch, saying,
"Bless this, O Lord, that with it thou mayst blow thine enemies to
tiny bits, in thy mercy." And the people did rejoice and did feast
upon the lambs and toads and tree-sloths and fruit-bats and orangutans
and breakfast cereals ... Now did the Lord say, "First thou pullest
the Holy Pin. Then thou must count to three. Three shall be the number
of the counting and the number of the counting shall be three. Four
shalt thou not count, neither shalt thou count two, excepting that
thou then proceedeth to three. Five is right out. Once the number
three, being the number of the counting, be reached, then lobbest
thou the Holy Hand Grenade in the direction of thine foe, who, being
naughty in my sight, shall snuff it."


:-)


Regards
Gaetano Mendola

Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.com> writes:
> > P.A.M. van Dam wrote:
> >> I'm looking for a command or method to find out what queries are
> >> currently being serviced by the database.
>
> > select * from pg_stat_activity;
>
> ... having first remembered to turn on stats_command_string; and
> thou shalt also be superuser.

And 7.5 will print out a more helpful message if you forget to set
these:

 test=> select * from pg_stat_activity;
  datid | datname | procpid | usesysid | usename  |        current_query         | query_start
 -------+---------+---------+----------+----------+------------------------------+-------------
  17227 | test    |   15527 |      100 | guest    | <command string not enabled> |
  17227 | test    |   15574 |        1 | postgres | <insufficient privilege>     |
 (2 rows)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073