Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > I think there are two ways of making this capability visible to users.
> > First, you could do:
> >
> > 	SET query_timeout = 5;
> >
> > and all queries after that would time out at 5 seconds.  Another option
> > is:
> >
> > 	BEGIN WORK TIMEOUT 5;
> > 	...
> > 	COMMIT;
> >
> > which would make the transaction timeout after 5 seconds.  We never
> > decided which one we wanted, or both.
> 
> Note that the first is a statement-level timeout and the second is a
> transaction-level timeout.  Be sure to clarify which one we want.

Oh, wow, that is an interesting distinction.  If there is a multi-query
transaction, do we time each query separately or the entire transaction?
I don't know which people want, and maybe this is why we need both GUC
and BEGIN WORK timeouts.  I don't remember this distinction in previous
discussions but it may be significant.  Of course, the GUC could behave
at a transaction level as well.  It will be tricky to manage multiple
alarms in a single process, but it can be done by creating an alarm
queue.

-- 
  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

Mon, 1 Apr 2002 12:48:45 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: timeout implementation issues
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ... It will be tricky to manage multiple
> alarms in a single process, but it can be done by creating an alarm
> queue.

I would argue that we should only support *one* kind of timeout, either
transaction-level or statement-level, so as to avoid that complexity.
I don't want to see us gilding the lily in the first implementation of
something that IMHO is of dubious usefulness in the first place.
We can think about extending the facility later, when and if it proves
sufficiently useful to justify more complexity.

I don't have a very strong feeling about whether transaction-level or
statement-level is more useful; am willing to do whichever one the
JDBC spec wants.

			regards, tom lane

Mon, 01 Apr 2002 13:00:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: timeout implementation issues 
On Mon, 1 Apr 2002, Bruce Momjian wrote:

> I don't know which people want, and maybe this is why we need both GUC
> and BEGIN WORK timeouts.  I don't remember this distinction in previous
> discussions but it may be significant.  Of course, the GUC could behave
> at a transaction level as well.  It will be tricky to manage multiple
> alarms in a single process, but it can be done by creating an alarm
> queue.

I think we should do just BEGIN WORK (transaction-level) timeouts; that is
all that the JDBC spec asks for. Does that sound good to people?

So the work that would need to be done is asking the driver to request the
timeout via "BEGIN WORK TIMEOUT 5"; getting the backend to parse that
request and set the alarm on each query in that transaction; getting the
backend to send a cancel request if the alarm goes off. I am right now in
the process of finding the place where BEGIN-level queries are parsed. Any
pointers to the right files to read would be appreciated.

j




Mon, 1 Apr 2002 13:12:02 -0500 (EST)
Jessica Perry Hekman <jphekman@dynamicdiagrams.com>
Re: timeout implementation issues
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > ... It will be tricky to manage multiple
> > alarms in a single process, but it can be done by creating an alarm
> > queue.
> 
> I would argue that we should only support *one* kind of timeout, either
> transaction-level or statement-level, so as to avoid that complexity.
> I don't want to see us gilding the lily in the first implementation of
> something that IMHO is of dubious usefulness in the first place.
> We can think about extending the facility later, when and if it proves
> sufficiently useful to justify more complexity.
> 
> I don't have a very strong feeling about whether transaction-level or
> statement-level is more useful; am willing to do whichever one the
> JDBC spec wants.

Agreed, only one timeout.  I just considered the statement/transaction
level quite interesting.  We could easily do GUC for query level, and
allow BEGIN WORK to override that for transaction level.  That would
give us the best of both worlds, if we want it.  I am not sure what
people are going to use this timeout for.  My guess is that only
transaction level is the way to go.

-- 
  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

Mon, 1 Apr 2002 13:18:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: timeout implementation issues
Jessica,

My reading of the JDBC spec would indicate that this is a statement 
level property (aka query level) since the method to enable this is on 
the Statement object and is named setQueryTimeout().  There is nothing I 
can find that would indicate that this would apply to the transaction in 
my reading of the jdbc spec.

thanks,
--Barry

Jessica Perry Hekman wrote:
> On Mon, 1 Apr 2002, Bruce Momjian wrote:
> 
> 
>>I don't know which people want, and maybe this is why we need both GUC
>>and BEGIN WORK timeouts.  I don't remember this distinction in previous
>>discussions but it may be significant.  Of course, the GUC could behave
>>at a transaction level as well.  It will be tricky to manage multiple
>>alarms in a single process, but it can be done by creating an alarm
>>queue.
> 
> 
> I think we should do just BEGIN WORK (transaction-level) timeouts; that is
> all that the JDBC spec asks for. Does that sound good to people?
> 
> So the work that would need to be done is asking the driver to request the
> timeout via "BEGIN WORK TIMEOUT 5"; getting the backend to parse that
> request and set the alarm on each query in that transaction; getting the
> backend to send a cancel request if the alarm goes off. I am right now in
> the process of finding the place where BEGIN-level queries are parsed. Any
> pointers to the right files to read would be appreciated.
> 
> j
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 



Mon, 01 Apr 2002 13:25:21 -0800
Barry Lind <barry@xythos.com>
Re: timeout implementation issues
Barry Lind writes:

> My reading of the JDBC spec would indicate that this is a statement
> level property (aka query level) since the method to enable this is on
> the Statement object and is named setQueryTimeout().  There is nothing I
> can find that would indicate that this would apply to the transaction in
> my reading of the jdbc spec.

Does it time out only queries or any kind of statement?

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 1 Apr 2002 18:22:26 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: timeout implementation issues
On Mon, 1 Apr 2002, Peter Eisentraut wrote:

> Does it time out only queries or any kind of statement?

Any kind, I believe.

FWIW, I took a look at the recommended JDBC driver for MySQL, hoping for
ideas; it does not implement query timeouts at all. I'll take a look at
mSQL next.

j


Mon, 1 Apr 2002 18:36:37 -0500 (EST)
Jessica Perry Hekman <jphekman@dynamicdiagrams.com>
Re: timeout implementation issues
The spec isn't clear on that point, but my interpretation is that it 
would apply to all types of statements not just queries.

--Barry

Peter Eisentraut wrote:
> Barry Lind writes:
> 
> 
>>My reading of the JDBC spec would indicate that this is a statement
>>level property (aka query level) since the method to enable this is on
>>the Statement object and is named setQueryTimeout().  There is nothing I
>>can find that would indicate that this would apply to the transaction in
>>my reading of the jdbc spec.
> 
> 
> Does it time out only queries or any kind of statement?
> 



Mon, 01 Apr 2002 15:41:32 -0800
Barry Lind <barry@xythos.com>
Re: timeout implementation issues
On Monday 01 April 2002 20:18, Bruce Momjian wrote:
> Tom Lane wrote:>
> Agreed, only one timeout. 
> ...

We have (at least) two ortogonal reasons why we want 
to abort a long running transaction:

- The long running transaction might compute a result 
  we are not interesed anymore (because it just takes
  too long to wait for the result). We do NOT always
  know in advance how patient we will be to wait for
  the result. Therefore I think the client should tell 
  the server, when his client (user?) got impatinet
  and aborted the whole transaction...

- The long running transaction might hold exclusive locks 
  and therefore decreases (or even nullifies) the overall 
  concurrency. We want to be able to disallow this by design.

I think a nice timout criteria would be a maximum lock time 
for all resources aquired exclusivly within a transaction. 
This would then affect transaction timeouts as well as statement 
timeouts with the advantage, the get concurrency guaratees.

Robert

Tue, 2 Apr 2002 10:58:13 +0200
Robert Schrem <robert.schrem@WiredMinds.de>
Re: timeout implementation issues, lock timeouts