Cancel query based on a timeout

Stijn Vanroye wrote:

Hi List,

I'm running a query on a not-so-small db. Mostly this query runs fast enough, but every once in a while the query takes a long time to complete in wich case the users start banging away on the keyboard :-).

What we would like to do is cancel the query after a certain time-out (e.g. 30 sec.)
Is there some way to send a command to the postgres sever to stop running the query?

We're using Postgres 7.3.4 and the latest ODBC driver. Programming is done with Borland Delphi 7

AFAIK, there is a statement timeout option but exactly which version brought it
in escapes me ATM...Most probably 7.4.x but don't take my word for it. Check you
r postgrersql.conf for details.

HTH

Shridhar

"Stijn Vanroye" <s.vanroye@farcourier.com> writes:

Is there some way to send a command to the postgres sever to stop running the query?

There's a statement_timeout parameter in recent releases. I forget
whether 7.3 has it though...

regards, tom lane

Thanks for the reply Shridhar and Tom.

But am I mistaken if setting this timeout parameter would affect all query's? That would not be the desired result. What I would like is to stop the execution of a specific (active) query if it is taking to long. If I execute the query, and it starts running, I would like to be able to abort that running query.

In pgAdmin III you have a 'stop' button when you are running a query (next to the little green 'start' arrow). I would like to be able to implement something similar in my application.

Regards,

Stijn Vanroye

Stijn Vanroye wrote:

Thanks for the reply Shridhar and Tom.

But am I mistaken if setting this timeout parameter would affect all query's? That would not be the desired result. What I would like is to stop the execution of a specific (active) query if it is taking to long. If I execute the query, and it starts running, I would like to be able to abort that running query.

In pgAdmin III you have a 'stop' button when you are running a query (next to the little green 'start' arrow). I would like to be able to implement something similar in my application.

You could put the database connection in asynchronous mode and use PQrequestCancel.

Please check http://www.postgresql.org/docs/7.3/static/libpq-async.html.

This is native C interface though. I have no idea to make it work with delphi
and/or ODBC.

HTH

Shridhar

Hi,

We are working on a similar problem - timeouts of long-running requests. We
are also currently using 7.3.4 for Postgres, but we are using Java, JDBC,
and PL/PGSql.

Question: Does Delphi have structured exception-handling? The solution
we've found requires the ability to descriminate between exception-types and
the ability to catch and throw execeptions in a safe fashion. If Delphi
does have SHE, then I think our solution could be mapped into Delphi without
much trouble.

The solution we've come up with is to add a "waiting procedure" table to the
database, along with some stored-procedures to manage the table. We call
this the "waitingproc" subsystem.

The "waitingproc" subsystem can then be used by client-side code (thru JDBC
calls; ODBC should be able to handle the particular subset we are dealing
with here) to detect when a server-side process overruns its alloted time.
The client then has the option to abort the transaction and/or restart the
server-side process.

Of course, the problem we are throwing the "waitingproc" subsystem at is not
your problem, but I think some of the core concepts/procedures are usuable.

Let me know if you'd like the Java and PL/PGSql source. The Java code is
fairly extensive and is mixed in with other business-logic, but I can help
you thru the rough parts.

Carl <|};-)>

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stijn Vanroye
Sent: Monday, May 10, 2004 3:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Cancel query based on a timeout

Hi List,

I'm running a query on a not-so-small db. Mostly this query runs fast
enough, but every once in a while the query takes a long time to complete in
wich case the users start banging away on the keyboard :-).

What we would like to do is cancel the query after a certain time-out (e.g.
30 sec.) Is there some way to send a command to the postgres sever to stop
running the query?

We're using Postgres 7.3.4 and the latest ODBC driver. Programming is done
with Borland Delphi 7

Regards,

Stijn Vanroye

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-----Original Message-----
From: Carl E. McMillin [mailto:carlymac@earthlink.net]
Sent: maandag 10 mei 2004 17:31
To: Stijn Vanroye; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Cancel query based on a timeout

Hi,

Also Hi,

We are working on a similar problem - timeouts of
long-running requests. We
are also currently using 7.3.4 for Postgres, but we are using
Java, JDBC,
and PL/PGSql.

Question: Does Delphi have structured exception-handling?
The solution
we've found requires the ability to descriminate between
exception-types and
the ability to catch and throw execeptions in a safe fashion.
If Delphi
does have SHE, then I think our solution could be mapped into
Delphi without
much trouble.

Delphi does indeed have some nice features for exception handling (and throwing).

The solution we've come up with is to add a "waiting
procedure" table to the
database, along with some stored-procedures to manage the
table. We call
this the "waitingproc" subsystem.

The "waitingproc" subsystem can then be used by client-side
code (thru JDBC
calls; ODBC should be able to handle the particular subset we
are dealing
with here) to detect when a server-side process overruns its
alloted time.
The client then has the option to abort the transaction
and/or restart the
server-side process.

When you give the client the option to abort and/or restart the server-side process, excactly how does you client do that? Is there a certain command, or do you use something inhereted in the transaction? You see, altough your solution seems to be a very usable and not to mention creative one, it might be a little much in our case, since we only have this problem in this one perticular case with one perticular query. By using threading we could let the client do the counting for the timeout, but we can't figure out how exactly you stop/reset a server-side proces (or query) from the client.

Of course, the problem we are throwing the "waitingproc"
subsystem at is not
your problem, but I think some of the core
concepts/procedures are usuable.

Let me know if you'd like the Java and PL/PGSql source. The
Java code is
fairly extensive and is mixed in with other business-logic,
but I can help
you thru the rough parts.

I would very much like to thank you for your answer/help. It's one of the most extensive ones I've got so far :-).
Actually I'm not the one tackeling this problem, I'm just the one following the postgresql mailinglist, but I've forwarded your answer to the right people. But I think that a solution to the problem on how to stop a query from the client is going to be sufficiënt.

Carl <|};-)>

Thanks very much,

Stijn Vanroye.

Hi Stijn,

..By using threading we could let the client do the counting for the

timeout,

but we can't figure out how exactly you stop/reset a server-side proces
(or query) from the client...

Check out the test-scenario I've attached. It demonstrates how to use Java,
JDBC, and threads to allow a timeout-thread to kill an overrunning SQL
statement. Please let me know if it satisfies. I'm assuming that your ODBC
stack is multithread-safe since the example requires one thread to abort the
SQL statement executing in an other thread. The example uses one database
("test") composed of one table ("table1") having one column ("field1"
INTEGER).

...I would very much like to thank you for your answer/help. It's one of

the most extensive ones I've got so far :-).

You are very welcome!

Carl <|};-)>

-----Original Message-----
From: Stijn Vanroye [mailto:s.vanroye@farcourier.com]
Sent: Tuesday, May 11, 2004 12:25 AM
To: pgsql-general@postgresql.org
Cc: Carl E. McMillin; Roy Janssen; Hassanein Altememy
Subject: RE: [GENERAL] Cancel query based on a timeout

-----Original Message-----
From: Carl E. McMillin [mailto:carlymac@earthlink.net]
Sent: maandag 10 mei 2004 17:31
To: Stijn Vanroye; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Cancel query based on a timeout

Hi,

Also Hi,

We are working on a similar problem - timeouts of
long-running requests. We
are also currently using 7.3.4 for Postgres, but we are using
Java, JDBC,
and PL/PGSql.

Question: Does Delphi have structured exception-handling?
The solution
we've found requires the ability to descriminate between
exception-types and
the ability to catch and throw execeptions in a safe fashion.
If Delphi
does have SHE, then I think our solution could be mapped into
Delphi without
much trouble.

Delphi does indeed have some nice features for exception handling (and
throwing).

The solution we've come up with is to add a "waiting
procedure" table to the
database, along with some stored-procedures to manage the
table. We call
this the "waitingproc" subsystem.

The "waitingproc" subsystem can then be used by client-side
code (thru JDBC
calls; ODBC should be able to handle the particular subset we
are dealing
with here) to detect when a server-side process overruns its
alloted time.
The client then has the option to abort the transaction
and/or restart the
server-side process.

When you give the client the option to abort and/or restart the server-side
process, excactly how does you client do that? Is there a certain command,
or do you use something inhereted in the transaction? You see, altough your
solution seems to be a very usable and not to mention creative one, it might
be a little much in our case, since we only have this problem in this one
perticular case with one perticular query. By using threading we could let
the client do the counting for the timeout, but we can't figure out how
exactly you stop/reset a server-side proces (or query) from the client.

Of course, the problem we are throwing the "waitingproc"
subsystem at is not
your problem, but I think some of the core
concepts/procedures are usuable.

Let me know if you'd like the Java and PL/PGSql source. The
Java code is
fairly extensive and is mixed in with other business-logic,
but I can help
you thru the rough parts.

I would very much like to thank you for your answer/help. It's one of the
most extensive ones I've got so far :-).
Actually I'm not the one tackeling this problem, I'm just the one following
the postgresql mailinglist, but I've forwarded your answer to the right
people. But I think that a solution to the problem on how to stop a query
from the client is going to be sufficiënt.

Carl <|};-)>

Thanks very much,

Stijn Vanroye.

Hi Carl,

Thanks a lot for sending me that code example. I have forwarded it to my colleague (as I said, It's not my project I'm just the one who's getting his mailbox bombarded with postgres mail :) ). He was kind of busy lately with another project so he couldn't delve into it right away. As soon as I hear from him I'll let you know if it's applicable in our situation.

About your statement:
/qoute
I'm assuming that your ODBC stack is multithread-safe since the example requires one thread to abort the SQL statement executing in another thread.
/end qoute
I don't quite understand what you mean by ODBC stack and it beïng multithread-safe. The threading is done in Delphi, so is handled by our own app.

Since it's not my project I haven't studied you code example in-depth, but it looks to me that there's gone some good work and thinking into it.

So once again, thank you for the (much apreciated) help.

Stijn Vanroye

FAR Courier B.V.
IT Department
Weerterveld 61
6231NC Meerssen
(The Netherlands)