Thread: How to stop a query
Hi. How can I abort a query that I see is listed in select * from pg_stat_activity;
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
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 >
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.
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
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.
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.
Hi Scott,
2012/7/19 Scott Marlowe <scott.marlowe@gmail.com>
On Thu, Jul 19, 2012 at 3:17 AM, younus <younus.essahli@gmail.com> wrote:NEVER kill -9 a postgres process unless you've exhausted all other
> Hi,
>
> First :
> ps -ef | grep postgres
> and kill -9 (PID of your query)
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.MUCH better way of doing things.
> Sec :
> select procpid, datname, usename, client_addr, current_query from
> pg_stat_activity where current_query!='<IDLE>';
>
> and
>
> SELECT pg_cancel_backend(procpid);
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>
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
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
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)
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
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
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