pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery kill
7+ messages / 4 participants
[nested] [flat]
* Query kill
@ 2002-07-12 05:00 Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Rudi Starcevic @ 2002-07-12 05:00 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kill
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
@ 2002-07-12 05:01 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-12 17:22 ` Re: Query kill eric soroos <eric-psql@soroos.net>
2002-07-12 17:53 ` Re: Query kill Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 7+ messages in thread
From: Bruce Momjian @ 2002-07-12 05:01 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; +Cc: pgsql-sql
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kill
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-07-12 17:22 ` eric soroos <eric-psql@soroos.net>
1 sibling, 0 replies; 7+ messages in thread
From: eric soroos @ 2002-07-12 17:22 UTC (permalink / raw)
To: pgsql-sql
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 query planner thinks will take 8M units of work. Generally, this is an unconstrained join between my biggest tables, a result that 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 of service issues.
eric
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kill
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-07-12 17:53 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-13 02:13 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Jan Wieck @ 2002-07-12 17:53 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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 #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kill
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-12 17:53 ` Re: Query kill Jan Wieck <JanWieck@Yahoo.com>
@ 2002-07-13 02:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-13 03:55 ` Re: Query kill Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2002-07-13 02:13 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kill
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-12 17:53 ` Re: Query kill Jan Wieck <JanWieck@Yahoo.com>
2002-07-13 02:13 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-07-13 03:55 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-13 04:27 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Jan Wieck @ 2002-07-13 03:55 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Rudi Starcevic <rudi@oasis.net.au>; 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 #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query kill
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-12 17:53 ` Re: Query kill Jan Wieck <JanWieck@Yahoo.com>
2002-07-13 02:13 ` Re: Query kill Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-13 03:55 ` Re: Query kill Jan Wieck <JanWieck@Yahoo.com>
@ 2002-07-13 04:27 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2002-07-13 04:27 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Rudi Starcevic <rudi@oasis.net.au>; 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-07-13 04:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-12 05:00 Query kill Rudi Starcevic <rudi@oasis.net.au>
2002-07-12 05:01 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-12 17:22 ` eric soroos <eric-psql@soroos.net>
2002-07-12 17:53 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-13 02:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-07-13 03:55 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-13 04:27 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox