Auto-timeout on all queries

Bruce Momjian wrote:

I would like to make one small request for future releases. Is there a
setup parameter that could be enabled that would put a timeout for a query
that runs too long or endangers the integrity of the system? With the
systems that I use, there are times that queries are entered that contain
errors or run so long that they eventually cause a system crash. It would
be most helpful if a timeout limit on queries could be enabled that would
stop a query after so much time and release the resources back to the system.

Does anyone thing this would be a nice feature? We can easily do it
with alarm()/elog(), but I want to know if it would be valuable.

Not so easy - we use SIGALRM in locking...

But why not just use CANCEL to stop too long running queries?

Vadim

Bruce Momjian wrote:

I would like to make one small request for future releases. Is there a
setup parameter that could be enabled that would put a timeout for a query
that runs too long or endangers the integrity of the system? With the
systems that I use, there are times that queries are entered that contain
errors or run so long that they eventually cause a system crash. It would
be most helpful if a timeout limit on queries could be enabled that would
stop a query after so much time and release the resources back to the system.

Does anyone thing this would be a nice feature? We can easily do it
with alarm()/elog(), but I want to know if it would be valuable.

Not so easy - we use SIGALRM in locking...

Oh. :-) I forgot.

But why not just use CANCEL to stop too long running queries?

Good question.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

From: Bruce Momjian <maillist@candle.pha.pa.us>
Date: Mon, 5 Jul 1999 21:19:54 -0400 (EDT)
CC: PostgreSQL-general <pgsql-general@postgreSQL.org>
Content-Type: text/plain; charset=US-ASCII
Sender: owner-pgsql-general@postgreSQL.org
Precedence: bulk

I would like to make one small request for future releases. Is there a
setup parameter that could be enabled that would put a timeout for a query
that runs too long or endangers the integrity of the system? With the
systems that I use, there are times that queries are entered that contain
errors or run so long that they eventually cause a system crash. It would
be most helpful if a timeout limit on queries could be enabled that would
stop a query after so much time and release the resources back to the system.

Does anyone thing this would be a nice feature? We can easily do it
with alarm()/elog(), but I want to know if it would be valuable.

-- 
Bruce Momjian                        |  http://www.op.net/~candle
maillist@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

I think we'd use it. At least on our development machines, which have
occasionally suffered from the ill effects of an excessively ambitious
query.

How would the interface to the user look?

--
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

I would like to make one small request for future releases. Is there a
setup parameter that could be enabled that would put a timeout for a query
that runs too long or endangers the integrity of the system? With the
systems that I use, there are times that queries are entered that contain
errors or run so long that they eventually cause a system crash. It would
be most helpful if a timeout limit on queries could be enabled that would
stop a query after so much time and release the resources back to the system.

Does anyone thing this would be a nice feature? We can easily do it
with alarm()/elog(), but I want to know if it would be valuable.

-- 
Bruce Momjian                        |  http://www.op.net/~candle
maillist@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

I think we'd use it. At least on our development machines, which have
occasionally suffered from the ill effects of an excessively ambitious
query.

How would the interface to the user look?

I assume it would be a new 'SET' option, like "SET TIMEOUT=60", but we
already use alarm() for something else, so it may be more difficult than
I thought. Not sure.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

On Mon, 5 Jul 1999, Bruce Momjian wrote:

I would like to make one small request for future releases. Is there a
setup parameter that could be enabled that would put a timeout for a query
that runs too long or endangers the integrity of the system? With the
systems that I use, there are times that queries are entered that contain
errors or run so long that they eventually cause a system crash. It would
be most helpful if a timeout limit on queries could be enabled that would
stop a query after so much time and release the resources back to the system.

Does anyone thing this would be a nice feature? We can easily do it
with alarm()/elog(), but I want to know if it would be valuable.

Oh yeah!

Time out in minutes, "time" out in tuples, all with an instructive error
message if exceeded. How about

SET TIMEOUT '5 min';
SET TUPLE LIMIT 10000;

If you can really "easily" do it, I say go for it.

--
Peter Eisentraut
PathWay Computing, Inc.

Been thinking about this time thing...wouldn't it be more flexible and more
useful to do it by transaction rather than a global server setting? Plus
time is a particularly bad measure as loaded servers take longer to execute
queries etc...

-----Original Message-----
From: Peter Eisentraut <peter@pathwaynet.com>
To: Bruce Momjian <maillist@candle.pha.pa.us>
Cc: Bob Kruger <bkruger@mindspring.com>; PostgreSQL-general
<pgsql-general@postgreSQL.org>
Date: Tuesday, July 06, 1999 5:26 PM
Subject: Re: [GENERAL] Auto-timeout on all queries

On Mon, 5 Jul 1999, Bruce Momjian wrote:

I would like to make one small request for future releases. Is there a
setup parameter that could be enabled that would put a timeout for a

query

that runs too long or endangers the integrity of the system? With the
systems that I use, there are times that queries are entered that

contain

errors or run so long that they eventually cause a system crash. It

would

be most helpful if a timeout limit on queries could be enabled that

would

stop a query after so much time and release the resources back to the

system.

Another nice feature would be a "guesstimate limit". So instead of the
query running a 1/2 hour and then timing out as taking too long, you get
an instant response "Me thinks this query is too hard. Get permission
from your sysadmin to run it".

Kane Tao wrote:

Since were making a wishlist on this topic (Though it may be harder
than it at first seemed), I'd like a SET option, with a
transaction-based override. That way, a casual query can be
intercepted, at least warning me of the long query if I forgot a
constraint on a join for instance. THen, after I've looked at it, I
may want to force it through. Or I may rewrite it and try running it
again under the same TIME/TUPLE limits.

Kane Tao wrote: