Re: Query kill - Mailing list pgsql-sql
From | Bruce Momjian |
---|---|
Subject | Re: Query kill |
Date | |
Msg-id | 200207130427.g6D4RXV18666@candle.pha.pa.us Whole thread Raw |
In response to | Re: Query kill (Jan Wieck <JanWieck@Yahoo.com>) |
List | pgsql-sql |
I assumed from the user's question that the admin just wanted to stop a specific query of a specific backend. Sending a SIGINT to the backend will do that. I wasn't talking client request or anything like that. Look at the description of the question below. --------------------------------------------------------------------------- Jan Wieck wrote: > Bruce Momjian wrote: > > > > Jan Wieck wrote: > > > Bruce Momjian wrote: > > > > > > > > Rudi Starcevic wrote: > > > > > Hello, > > > > > > > > > > If I write a query that is inefficient or in an eternal loop how > > > > > do I stop it without restarting the postmaster ? > > > > > > > > > > I can see many postmaster processed appearing in the output of the 'ps' > > > > > command. > > > > > Do I need to stop/kill them all or can I stop just the query I want ? > > > > > > > > Just send a SIGINT to the process. That simulates a ^C, which works too > > > > from the client like psql. > > > > > > Doesn't the client need a signal handler for that and call > > > PQcancelRequest() in that? > > > > Every backend has that signal handler defined, I think. > > What? > > So you suggest that the client application, that actually want's > to cancel it's query, send's a SIGINT signal to the backend it is > connected to? > > Bruce! To do so would require to be the PostgreSQL UNIX user on > the database server! > > What I was talking about is the > > PQcancelRequest(PGconn *conn) > > function in libpq. This "client side" function opens another > connection to the postmaster, using the same host and port as > "conn" did. Then it sends a cancel request startup packet > containing this connections backend pid and cancel key (only > known if the client talks version 2 or above of the protocol). > > The spawned off backend process receiving the cancel request > startup packet on the "server side" checks the postmasters > process list for validity of the pid and cancel key combination > and sends the backend a signal. It is allowed to do so because it > is a child of the postmaster, as the backend it is signalling is, > both running under the same userid. > > So yes, every backend has that signal handler. But not everyone > has a single user environment, where every process is allowed to > kill everybody else. The client by default does not have any > signal handler. So it could catch SIGALRM, do an alarm(10), do > PQexec() and alarm(0). If the signal handler get's called, it'll > call PQcancelRequest() causing PQexec() to recieve an ERROR (the > message says something like "query canceled" or so). > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being > right. # > # Let's break this rule - forgive > me. # > #================================================== > JanWieck@Yahoo.com # > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026