How to stop a query

Started by A Bover 16 years ago14 messagesgeneral

Jump to latest

A B

gentosaker@gmail.com

over 16 years ago

Hi.
How can I abort a query that I see is listed in

select * from pg_stat_activity;

Guillaume Lelarge

guillaume@lelarge.info

over 16 years ago

In reply to: A B (#1)

Re: How to stop a query

Le vendredi 4 septembre 2009 à 07:37:20, A B a écrit :

Hi.
How can I abort a query that I see is listed in

select * from pg_stat_activity;

You have to do:

SELECT pg_cancel_backend(pid of the postgres process);

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Pavel Stehule

pavel.stehule@gmail.com

over 16 years ago

In reply to: A B (#1)

Re: How to stop a query

hello

2009/9/4 A B <gentosaker@gmail.com>:

Hi.
How can I abort a query that I see is listed in

select * from pg_stat_activity;

look on pg_cancel_backend function

http://www.postgresql.org/docs/8.2/static/functions-admin.html

regards
Pavel Stehule

Show quoted text

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

younus

younus.essahli@gmail.com

over 13 years ago

In reply to: Pavel Stehule (#3)

Re: How to stop a query

Hi,

First :
ps -ef | grep postgres
and kill -9 (PID of your query)

Sec :
select procpid, datname, usename, client_addr, current_query from
pg_stat_activity where current_query!='<IDLE>';

and

SELECT pg_cancel_backend(procpid);

younus,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-stop-a-query-tp1924086p5717227.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Atri Sharma

atri.jiit@gmail.com

over 13 years ago

In reply to: younus (#4)

Re: How to stop a query

On Thu, Jul 19, 2012 at 2:47 PM, younus <younus.essahli@gmail.com> wrote:

Hi,

First :
ps -ef | grep postgres
and kill -9 (PID of your query)

Sec :
select procpid, datname, usename, client_addr, current_query from
pg_stat_activity where current_query!='<IDLE>';

and

SELECT pg_cancel_backend(procpid);

younus,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-stop-a-query-tp1924086p5717227.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I am not too sure if it is applicable,but have you tried Control-C?

Atri

--
Regards,

Atri
l'apprenant

Scott Marlowe

scott.marlowe@gmail.com

over 13 years ago

In reply to: younus (#4)

Re: How to stop a query

On Thu, Jul 19, 2012 at 3:17 AM, younus <younus.essahli@gmail.com> wrote:

Hi,

First :
ps -ef | grep postgres
and kill -9 (PID of your query)

NEVER kill -9 a postgres process unless you've exhausted all other
possibilities, as it forces a restart of all the other backends as
well. A plain kill (no -9) is usually all you need, and it doesn't
cause all the other backends to restart and flush all shared memory.

Sec :
select procpid, datname, usename, client_addr, current_query from
pg_stat_activity where current_query!='<IDLE>';

and

SELECT pg_cancel_backend(procpid);

MUCH better way of doing things.

younus

younus.essahli@gmail.com

over 13 years ago

In reply to: Atri Sharma (#5)

Re: How to stop a query

Hi,

Yes, I'm sure, it's work.

if you execute query by another program (program java), you must use the
first solution [ps -ef | grep postgres and kill -9 (PID of your query)].

if you use pgsql terminal and you're connecting with postgres you can use
select procpid, datname, usename, client_addr, current_query from
pg_stat_activity where current_query!='<IDLE>';
SELECT pg_cancel_backend (procpid);

Younus.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-stop-a-query-tp1924086p5717297.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

younus

younus.essahli@gmail.com

over 13 years ago

In reply to: Scott Marlowe (#6)

Re: How to stop a query

Hi Scott,

thank you for your comment

2012/7/19 Scott Marlowe <scott.marlowe@gmail.com>

Show quoted text

On Thu, Jul 19, 2012 at 3:17 AM, younus <younus.essahli@gmail.com> wrote:

Hi,

First :
ps -ef | grep postgres
and kill -9 (PID of your query)

NEVER kill -9 a postgres process unless you've exhausted all other
possibilities, as it forces a restart of all the other backends as
well. A plain kill (no -9) is usually all you need, and it doesn't
cause all the other backends to restart and flush all shared memory.

Sec :
select procpid, datname, usename, client_addr, current_query from
pg_stat_activity where current_query!='<IDLE>';

and

SELECT pg_cancel_backend(procpid);

MUCH better way of doing things.

Martin French

Martin.French@romaxtech.com

over 13 years ago

In reply to: younus (#7)

Re: How to stop a query

<html><body>As Scott mentioned, kill -9 on a Postgres process is not a wise idea on a Postgres process. If you query is coming from another application, then terminating that application with a kill -9 *may* work, but is, as scott says, a last resort I tend to use kill -TERM (15) to disconnect the client, which gives the log message " terminating connection due to administrator command" kill -INT (2) gives the "cancelling statement due to user request" and does not disconnect the client. So it depends on what you want to do. If i have a runaway query (not so common on 9.1 now), Then i'll try the above, and if they don't work, then i'll try an /etc/init.d/postgresql stop or a pg_ctl stop -m f. then restart the server. Only if that does not work will i consider killing using a -9. Cheers <tt>pgsql-general-owner@postgresql.org wrote on 19/07/2012 17:25:57: > From: younus <younus.essahli@gmail.com></tt> <tt>> To: pgsql-general@postgresql.org, </tt> <tt>> Date: 19/07/2012 20:30</tt> <tt>> Subject: Re: [GENERAL] How to stop a query</tt> <tt>> Sent by: pgsql-general-owner@postgresql.org</tt> <tt>> > Hi, > > Yes, I'm sure, it's work. > > if you execute query by another program (program java), you must use the > first solution [ps -ef | grep postgres and  kill -9 (PID of your query)]. > > if you use pgsql terminal and you're connecting with postgres you can use > select procpid, datname, usename, client_addr,  current_query from > pg_stat_activity where current_query!='<IDLE>'; > SELECT pg_cancel_backend (procpid); > > > > > Younus. > > -- > View this message in context: <a href="http://postgresql">http://postgresql</a>. &gt; 1045698.n5.nabble.com/How-to-stop-a-query-tp1924086p5717297.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > <a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a> &gt; </tt>=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
</body></html>

#10

Gary Fu

gfu@sigmaspace.com

over 12 years ago

In reply to: Martin French (#9)

autovaccum task got cancelled

Hello,

I'm running an application (with programs in Perl) through pgpool 3.1
with replication mode to two postgresql db servers (version 9.0.13).
Recently, I noticed that the following messages repeatedly showed in
postgres log files. As far as I know, the application programs do not
make any specific lock on the 'file' table. I'm not sure if it is
caused by the pgpool or something else.

Thanks for any help in advance.
Gary

2013-10-31 17:58:56 UTCDETAIL: Process 8580 waits for
ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 17:58:56 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW
EXCLUSIVE MODE
2013-10-31 17:58:56 UTCERROR: canceling autovacuum task
2013-10-31 17:58:56 UTCCONTEXT: automatic vacuum of table
"sd3ops1.public.file"
2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614
2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for
ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW
EXCLUSIVE MODE
2013-10-31 18:01:30 UTCERROR: canceling autovacuum task
2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table
"sd3ops1.public.file"
2013-10-31 18:01:49 UTCLOG: could not receive data from client:
Connection reset by peer
2013-10-31 18:01:49 UTCLOG: unexpected EOF within message length word
2013-10-31 18:02:04 UTCLOG: sending cancel to blocking autovacuum PID 8753
2013-10-31 18:02:04 UTCDETAIL: Process 8777 waits for
ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 18:02:04 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW
EXCLUSIVE MODE
2013-10-31 18:02:04 UTCERROR: canceling autovacuum task
2013-10-31 18:02:04 UTCCONTEXT: automatic vacuum of table
"sd3ops1.public.file"
2013-10-31 18:03:09 UTCLOG: sending cancel to blocking autovacuum PID 8782
2013-10-31 18:03:09 UTCDETAIL: Process 8806 waits for
ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 18:03:09 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW
EXCLUSIVE MODE
2013-10-31 18:03:09 UTCERROR: canceling autovacuum task
2013-10-31 18:03:09 UTCCONTEXT: automatic vacuum of table
"sd3ops1.public.file"
2013-10-31 18:04:04 UTCLOG: sending cancel to blocking autovacuum PID 8810
2013-10-31 18:04:04 UTCDETAIL: Process 8395 waits for
ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 18:04:04 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW
EXCLUSIVE MODE

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#11

bricklen

bricklen@gmail.com

over 12 years ago

In reply to: Gary Fu (#10)

Re: autovaccum task got cancelled

On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu <gfu@sigmaspace.com> wrote:

Hello,

I'm running an application (with programs in Perl) through pgpool 3.1 with
replication mode to two postgresql db servers (version 9.0.13). Recently,
I noticed that the following messages repeatedly showed in postgres log
files. As far as I know, the application programs do not make any specific
lock on the 'file' table. I'm not sure if it is caused by the pgpool or
something else.

Try setting your log_line_prefix so that more details are logged, that
might help track down where the locks etc are coming from.
Eg
log_line_prefix = '%m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x]
[%i] '

and reload your config (eg. "select pg_reload_conf()" as the superuser)

#12

Sergey Konoplev

gray.ru@gmail.com

over 12 years ago

In reply to: Gary Fu (#10)

Re: autovaccum task got cancelled

On Thu, Oct 31, 2013 at 11:51 AM, Gary Fu <gfu@sigmaspace.com> wrote:

I'm running an application (with programs in Perl) through pgpool 3.1 with
replication mode to two postgresql db servers (version 9.0.13). Recently, I
noticed that the following messages repeatedly showed in postgres log files.
As far as I know, the application programs do not make any specific lock on
the 'file' table. I'm not sure if it is caused by the pgpool or something
else.

[...]

2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614
2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for ShareRowExclusiveLock
on relation 11959608 of database 596746.
2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW EXCLUSIVE
MODE
2013-10-31 18:01:30 UTCERROR: canceling autovacuum task
2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table
"sd3ops1.public.file"

From the release notes to 9.0.12 [1]http://www.postgresql.org/docs/9.0/static/release-9-0-12.html:

<<Fix performance problems with autovacuum truncation in busy
workloads (Jan Wieck)

Truncation of empty pages at the end of a table requires exclusive
lock, but autovacuum was coded to fail (and release the table lock)
when there are conflicting lock requests. Under load, it is easily
possible that truncation would never occur, resulting in table bloat.
Fix by performing a partial truncation, releasing the lock, then
attempting to re-acquire the lock and continue. This fix also greatly
reduces the average time before autovacuum releases the lock after a
conflicting request arrives.>>

[1]: http://www.postgresql.org/docs/9.0/static/release-9-0-12.html

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#13

Kevin Grittner

Kevin.Grittner@wicourts.gov

over 12 years ago

In reply to: Sergey Konoplev (#12)

Re: autovaccum task got cancelled

Sergey Konoplev <gray.ru@gmail.com> wrote:

As far as I know, the application programs do not make any
specific lock on the 'file' table. I'm not sure if it is caused
by the pgpool or something else.

[...]

2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614
2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW EXCLUSIVE MODE
2013-10-31 18:01:30 UTCERROR: canceling autovacuum task
2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table "sd3ops1.public.file"

From the release notes to 9.0.12:

<<Fix performance problems with autovacuum truncation in busy
workloads (Jan Wieck)

I don't think the problem described here has anything to do with
that. It looks to me like there is an explicit LOCK TABLE
statement being executed for a mode which conflicts with a normal
vacuum or analyze, even without truncation. The cited change
*avoids* this sort of cancellation for the truncation phase, so it
is not getting that far.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#14

Gary Fu

gfu@sigmaspace.com

over 12 years ago

In reply to: Kevin Grittner (#13)

Re: autovaccum task got cancelled

On 11/01/13 03:23, Kevin Grittner wrote:

Sergey Konoplev <gray.ru@gmail.com> wrote:

As far as I know, the application programs do not make any
specific lock on the 'file' table. I'm not sure if it is caused
by the pgpool or something else.

[...]

2013-10-31 18:01:30 UTCLOG: sending cancel to blocking autovacuum PID 8614
2013-10-31 18:01:30 UTCDETAIL: Process 8677 waits for ShareRowExclusiveLock on relation 11959608 of database 596746.
2013-10-31 18:01:30 UTCSTATEMENT: LOCK TABLE "file" IN SHARE ROW EXCLUSIVE MODE
2013-10-31 18:01:30 UTCERROR: canceling autovacuum task
2013-10-31 18:01:30 UTCCONTEXT: automatic vacuum of table "sd3ops1.public.file"

From the release notes to 9.0.12:

<<Fix performance problems with autovacuum truncation in busy
workloads (Jan Wieck)

I don't think the problem described here has anything to do with
that. It looks to me like there is an explicit LOCK TABLE
statement being executed for a mode which conflicts with a normal
vacuum or analyze, even without truncation. The cited change
*avoids* this sort of cancellation for the truncation phase, so it
is not getting that far.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Thanks for all the replies. I'm pretty sure right now, it is the pgpool
since I searched the pgpool source codes and found those strings.
Also, I have the pgpool configuration 'insert_lock' on (by default),
but without applying the 'insert_lock.sql' as pgpool suggested.

However, I don't know why it did not happen before. By the way,
I think Kevin is right, since the problem happened to our test instance
also and it is with postgres 9.2.4.

For pgpool, if anyone knows that if I can apply the 'insert_lock.sql' when
the pgpool is still running (maybe I should ask this in pgpool groups) ?

Thanks,
Gary

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general