Re: Cancel query based on a timeout

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)

> -----Original Message-----
> From: Carl E. McMillin [mailto:carlymac(at)earthlink(dot)net]
> Sent: dinsdag 11 mei 2004 20:03
> To: Stijn Vanroye
> Cc: pgsql-general(at)postgresql(dot)org; Bob; 'Bill Martin'; Joe
> Burks; verbus
> counts
> Subject: RE: [GENERAL] Cancel query based on a timeout
>
>
> 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(dot)vanroye(at)farcourier(dot)com]
> Sent: Tuesday, May 11, 2004 12:25 AM
> To: pgsql-general(at)postgresql(dot)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(at)earthlink(dot)net]
> > Sent: maandag 10 mei 2004 17:31
> > To: Stijn Vanroye; pgsql-general(at)postgresql(dot)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.
>
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> Stijn Vanroye
> > Sent: Monday, May 10, 2004 3:55 AM
> > To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so
> > that your
> > message can get through to the mailing list cleanly
> >
> >
> >
>