Обсуждение: Query kill
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 ? Thanks heaps Rudi.
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. -- 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
On Fri, 12 Jul 2002 01:01:31 -0400 (EDT) in message <200207120501.g6C51Vo14062@candle.pha.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>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. Is there a way to deny permission for certain users to execute a query that exceeds some expected cost? For example, I have a query builder from user input that could produce a query that ends up doing something that the queryplanner thinks will take 8M units of work. Generally, this is an unconstrained join between my biggest tables, a resultthat is neither fast nor useful. If I could set a threshold of 1M units for the webapp user, I could trap this sort of thing before they cause quality ofservice issues. eric
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? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
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. -- 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
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 #
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