Re: Query kill - Mailing list pgsql-sql

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 #