Thread: How to stop a query

How to stop a query

From
A B
Date:
Hi.
How can I abort a query that I see is listed in

select * from pg_stat_activity;

Re: How to stop a query

From
Guillaume Lelarge
Date:
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

Re: How to stop a query

From
Pavel Stehule
Date:
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

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

Re: How to stop a query

From
younus
Date:
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.

Re: How to stop a query

From
Atri Sharma
Date:
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

Re: How to stop a query

From
Scott Marlowe
Date:
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.

Re: How to stop a query

From
younus
Date:
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.

Re: How to stop a query

From
Younus
Date:
Hi Scott, 

thank you for your comment


2012/7/19 Scott Marlowe <scott.marlowe@gmail.com>
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.

Re: How to stop a query

From
"Martin French"
Date:
As Scott mentioned, kil=
l -9 on a Postgres process is not a wise idea on a Postgres process.=
If you query is coming from an=
other application, then terminating that application with a kill -9 *may* w=
ork, but is, as scott says, a last resort<font size=3D"2" fa=
ce=3D"sans-serif">I tend to use kill -TERM (15) to disconnect the client, w=
hich gives the log message " terminating connection due to administrat=
or command"kill -INT (=
2) gives the "cancelling statement due to user request" and does =
not disconnect the client.<font size=3D"2" face=3D"sans-seri=
f">So it depends on what you want to do.<font size=3D"2" fac=
e=3D"sans-serif">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/p=
ostgresql stop or a pg_ctl stop -m f. then restart the server.<b=
r>Only if that does not work will i co=
nsider killing using a -9.<font size=3D"2" face=3D"sans-seri=
f">Cheerspgsql-general-owner@postgresql=
.org wrote on 19/07/2012 17:25:57:> From: younus <younus.essa=
hli@gmail.com>> To: pgsql-genera=
l@postgresql.org, > Date: 19/07/201=
2 20:30> Subject: Re: [GENERAL] How=
 to stop a query> Sent by: pgsql-ge=
neral-owner@postgresql.org> >=
; Hi, > > Yes, I'm sure, it's work.> > if you e=
xecute query by another program (program java), you must use the> fi=
rst solution [ps -ef | grep postgres and  kill -9 (PID of your query)]=
.> > if you use pgsql terminal and you're connecting with pos=
tgres you can use > select procpid, datname, usename, client_addr, &=
nbsp;current_query from> pg_stat_activity where current_query!=3D'&l=
t;IDLE>';> SELECT pg_cancel_backend (procpid);> > <=
br>> > > Younus.> > --> View this mes=
sage in context: http://postgresql.&g=
t; 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-genera=
l@postgresql.org)> To make changes to your subscription:> <a =
href=3D"http://www.postgresql.org/mailpref/pgsql-general">http://www.postgr=
esql.org/mailpref/pgsql-general> <font face=3D"s=
ans-serif">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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

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

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

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Confidentiality Statement
This transmission is for the addressee only and contains information that i=
s confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf o=
f the addressee=20
you may not copy or use it, or disclose it to anyone else.=20
If you have received this transmission in error please delete from your sys=
tem and contact the sender. Thank you for your cooperation.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</f=
ont>

autovaccum task got cancelled

From
Gary Fu
Date:
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



Re: autovaccum task got cancelled

From
bricklen
Date:
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)

Re: autovaccum task got cancelled

From
Sergey Konoplev
Date:
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]:

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


Re: autovaccum task got cancelled

From
Kevin Grittner
Date:
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


Re: autovaccum task got cancelled

From
Gary Fu
Date:
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