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: 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: 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.
> -----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: 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
>
>
>
package dbtst;
import java.lang.Object;
import java.lang.String;
import java.lang.Class;
import java.lang.Thread;
import java.lang.Exception;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
/**
* Written by Carl E. McMillin
* CarlCo, carlymac@earthlink.net
* on 05/11/2004.
* */
public class driver extends Object {
public final static String
jdbcDriver = "org.postgresql.Driver",
dbUrl = "jdbc:postgresql://192.168.0.20/test?user=postgres&password=";
public driver() {
}
/**
* Thread to lock the table in this test.
* */
private class LockThread extends Thread {
public LockThread() {
}
public void run() {
System.out.println("Lock thread startup.");
System.out.flush();
Object waitObj = new Object();
try {
//Create the connection and set autocommit to false.
Connection conn = DriverManager.getConnection(dbUrl);
conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
System.out.println("Lock thread locking table.");
System.out.flush();
//Statement to explicitly lock the table for exclusive access.
PreparedStatement ps1 =
conn.prepareStatement("LOCK table1 IN ACCESS EXCLUSIVE MODE");
ps1.execute();
ps1.close();
System.out.println("Lock thread selecting all records from table," +
" but never reading the ResultSet.");
System.out.flush();
/* Statement to read records for UPDATE. This should force the lock
to be active. */
PreparedStatement ps2 =
conn.prepareStatement("SELECT * FROM table1 FOR UPDATE");
ResultSet rs = ps2.executeQuery();
if (rs != null) {
rs.next();
synchronized (waitObj) {
/* Wait 30 seconds. This is enough time to bracket both
BlockedThread and TimeoutThread */
waitObj.wait(30000L);
}
rs.close();
}
ps2.close();
conn.close();
}
catch (Exception ex) {
System.err.println("Lockout thread exception:");
System.err.flush();
ex.printStackTrace();
}
catch (Error err) {
System.err.println("Lockout thread error:");
System.err.flush();
err.printStackTrace();
}
finally {
System.out.println("Lock thread shutdown.");
System.out.flush();
}
}
}
/**
* Thread executing a statement client-side, guarded by a TimeoutThread.
* The timeout thread will kill the statement (thus the executing thread)
* if the timeout occurs before the statement completes. In this test,
* the statement NEVER completes before the timeout.
* */
private class BlockedThread extends Thread {
public BlockedThread(Connection withConn,
PreparedStatement withStmt) {
conn = withConn;
stmt = withStmt;
}
public void run() {
System.out.println("Blocked thread startup.");
System.out.flush();
Object waitObj = new Object();
try {
synchronized (waitObj) {
//Wait ten seconds.
waitObj.wait(10000L);
}
System.out.println("Blocked thread attempting to execute statement " +
"which should block since we locked the table.");
System.out.flush();
//Execute the statement which should be blocked.
stmt.execute();
stmt.close();
//Commit changes.
conn.commit();
conn.close();
}
catch (Exception ex) {
System.err.println("Blocked thread exception:");
System.err.flush();
ex.printStackTrace();
}
catch (Error err) {
System.err.println("Blocked thread error:");
System.err.flush();
err.printStackTrace();
}
finally {
System.out.println("Blocked thread shutdown.");
System.out.flush();
}
}
private Connection conn;
private PreparedStatement stmt;
}
/**
* Thread guarding a BlockedThread. The timeout thread will kill the blocked
* thread if the timeout occurs before the blocked-thread completes. In
* this test, the blocked-thread NEVER completes before the timeout.
* */
private class TimeoutThread extends Thread {
public TimeoutThread(Connection withConn,
PreparedStatement withStmt) {
conn = withConn;
stmt = withStmt;
}
public void run() {
System.out.println("Timeout thread startup.");
System.out.flush();
Object waitObj = new Object();
try {
synchronized (waitObj) {
/* Wait twenty seconds. This gives the blocked thread time to
try its execution. */
waitObj.wait(20000L);
}
System.out.println("Timeout thread killing blocked thread statement.");
System.out.flush();
//Kill the statement.
stmt.cancel();
stmt.close();
//Close the connection.
conn.rollback();
conn.close();
}
catch (Exception ex) {
System.err.println("Timeout thread exception:");
System.err.flush();
ex.printStackTrace();
}
catch (Error err) {
System.err.println("Timeout thread error:");
System.err.flush();
err.printStackTrace();
}
finally {
System.out.println("Timeout thread shutdown.");
System.out.flush();
}
}
private Connection conn;
private PreparedStatement stmt;
}
/**
* Client-side test of killing a blocked SQL query-execution if it
* exceeds its allowed running time.
* */
public void test() {
Object waitObj = new Object();
try {
//Try to load JDBC driver.
Class.forName(jdbcDriver).newInstance();
//Create the connection and set autocommit to false.
Connection conn = DriverManager.getConnection(dbUrl);
conn.setTransactionIsolation(conn.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
//Statement to insert a record into table.
PreparedStatement ps =
conn.prepareStatement("INSERT INTO table1 VALUES (?)");
ps.setInt(1, 1);
//Lock the table.
new LockThread().start();
//Wait a bit to make sure lock is definitely started.
synchronized (waitObj) {
waitObj.wait(5000L);
}
/* Create a thread to execute the insert-statement. The thread will
never get a chance to finish executing the statement. The lock-thread
holds exclusive access to the table and the TimeoutThread is going
to cancel the statement before the lock is released. */
new BlockedThread(conn, ps).start();
//Create a thread to time-out the blocked thread.
new TimeoutThread(conn, ps).start();
}
catch (Exception ex) {
ex.printStackTrace();
}
catch (Error err) {
err.printStackTrace();
}
}
public static void main(String[] args) {
new driver().test();
}
}
---------------------------(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
