timeout implementation issues
I have been talking with Bruce Momjian about implementing query
timeouts in the JDBC driver. As things stand, you can call
setQueryTimeout() or getQueryTimeout(), but a slow query will never
actually timeout, even if a timeout is set. The result of a timeout
should be a SQLException.
Bruce feels that this should be implemented in the backend: set an
alarm() in the backend on transaction start, then call the query
cancel() code if the alarm() goes off, and reset the alam if the query
finishes before the timeout.
I am concerned that this method does not provide a means of triggering
the SQLException in the driver. For an example, look at how cancel is
implemented (org.postgresql.Connection::cancelQuery()): we create a
new PG_Stream and send some integers to it which represent the cancel
request. Then we close the PG_Stream. There is no point at which we
receive any notification from the backend that the query has been
cancelled.
I looked in postmaster.c, processCancelRequest() to see what the
backend does. A SIGINT is sent to the backend when the cancel request
is successfully fulfilled, but nothing seems to be sent to the
interface.
One possibility is that the driver might just notice that the connection
has closed, and throw an Exception then. javax.sql.PooledConnection has an
addConnectionEventListener() method; we could add a
ConnectionEventListener there which would throw an Exception when the
connection closes.
In practice, this may or may not be a good idea. The place to get hold
of a PooledConnection seems to be in XAConnectionImpl (I am not sure
how the driver would actually request the relevant XAConnectionImpl
object, but I am sure I could figure that out). The thing is that this
class only allows one ConnectionEventListener to be set, so if we set
it, the user would be out of luck if he wanted to add his own
listener.
My proposal, then, is that the Java driver should submit the
transaction request; wait for the timeout; if it goes off, submit a
cancel request; and then throw a SQLException. We would not handle
this in the backend at all.
Bruce agreed that this was a good point to ask what the rest of the
hackers list thought. Any input?
Thanks,
Jessica
Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
[snip]
My proposal, then, is that the Java driver should submit the
transaction request; wait for the timeout; if it goes off, submit a
cancel request; and then throw a SQLException. We would not handle
this in the backend at all.
Bruce agreed that this was a good point to ask what the rest of the
hackers list thought. Any input?
I guess the $64 question is whether any frontends other than JDBC want
this behavior. If it's JDBC-only then I'd certainly vote for making
JDBC handle it ... but as soon as we see several different frontends
implementing similar behavior, I'd say it makes sense to implement it
once in the backend.
So, what's the market?
regards, tom lane
Tom Lane wrote:
Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
[snip]
My proposal, then, is that the Java driver should submit the
transaction request; wait for the timeout; if it goes off, submit a
cancel request; and then throw a SQLException. We would not handle
this in the backend at all.Bruce agreed that this was a good point to ask what the rest of the
hackers list thought. Any input?I guess the $64 question is whether any frontends other than JDBC want
this behavior. If it's JDBC-only then I'd certainly vote for making
JDBC handle it ... but as soon as we see several different frontends
implementing similar behavior, I'd say it makes sense to implement it
once in the backend.So, what's the market?
There is clearly interest from all interfaces. This item has been
requested quite often, usually related to client apps or web apps.
--
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 19026On Sat, 30 Mar 2002, Bruce Momjian wrote:
There is clearly interest from all interfaces. This item has been
requested quite often, usually related to client apps or web apps.
I definitely agree that implementing it in the backend would be the best
plan, if it's feasible. I just can't figure out how to pass information
back to the driver that the request has been cancelled (and that, in
JDBC's case, a SQLException should be thrown). Any thoughts about that?
j
Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
I definitely agree that implementing it in the backend would be the best
plan, if it's feasible. I just can't figure out how to pass information
back to the driver that the request has been cancelled (and that, in
JDBC's case, a SQLException should be thrown). Any thoughts about that?
Why would this be any different from a cancel-signal-instigated abort?
You'd be reporting elog(ERROR) in any case.
regards, tom lane
On Sat, 30 Mar 2002, Tom Lane wrote:
Why would this be any different from a cancel-signal-instigated abort?
You'd be reporting elog(ERROR) in any case.
If I understand the code correctly, in the case of a cancel signal, the
driver sends the signal and then assumes that the backend has accepted it
and cancelled; the back end does not report back. In this case, the driver
would not be sending a signal, so it would not know that the process had
reached the timeout and stopped (and it needs to know that). What we
*could* do is have *both* the driver and the backend run timers and both
stop when the timeout is reached. This seems like a solution just begging
to produce ugly bugs, though -- and if we have to implement such a wait in
the driver, we may as well implement the whole thing in the driver and
just have it send a cancel signal when it times out.
Or am I misunderstanding the situation?
j
On Sat, 30 Mar 2002, Tom Lane wrote:
Au contraire, it is not assuming anything. It is sending off a cancel
request and then waiting to see what happens. Maybe the query will be
canceled, or maybe it will complete normally, or maybe it will fail
because of some error unrelated to the cancel request. In any case the
backend *will* eventually report completion/error status, and the
frontend does not assume anything until it gets that report.
Ah, okay; this was not my understanding. I'll look at the code again.
Why does it need to know that? When it gets the error report back, it
can notice that the error says "Query aborted by timeout" (or however we
phrase it) ... but I'm not seeing why it should care.
I just meant it needed to know that the process had stopped prematurely; I
didn't mean it needed to know why.
I'll get back to you after doing a little more research.
j
Import Notes
Reply to msg id not found: 2861.1017516771@sss.pgh.pa.us | Resolved by subject fallback
Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
If I understand the code correctly, in the case of a cancel signal, the
driver sends the signal and then assumes that the backend has accepted it
and cancelled; the back end does not report back.
Au contraire, it is not assuming anything. It is sending off a cancel
request and then waiting to see what happens. Maybe the query will be
canceled, or maybe it will complete normally, or maybe it will fail
because of some error unrelated to the cancel request. In any case the
backend *will* eventually report completion/error status, and the
frontend does not assume anything until it gets that report.
In this case, the driver
would not be sending a signal, so it would not know that the process had
reached the timeout and stopped (and it needs to know that).
Why does it need to know that? When it gets the error report back, it
can notice that the error says "Query aborted by timeout" (or however we
phrase it) ... but I'm not seeing why it should care.
regards, tom lane
On Sat, 30 Mar 2002, Tom Lane wrote:
Au contraire, it is not assuming anything. It is sending off a cancel
request and then waiting to see what happens. Maybe the query will be
Okay, I see now: when processCancelRequest() is called, a return of 127 is
sent. That would indeed work; thanks for walking me through it.
My other question was how to send the timeout value to the backend. Bruce
said at one point:
Timeout can be part of BEGIN, or a SET value, which would work from
jdbc.
I'm not sure how this would work. The timeout value would be sent as part
of a SQL query?
j
Jessica Perry Hekman wrote:
On Sat, 30 Mar 2002, Tom Lane wrote:
Au contraire, it is not assuming anything. It is sending off a cancel
request and then waiting to see what happens. Maybe the query will beOkay, I see now: when processCancelRequest() is called, a return of 127 is
sent. That would indeed work; thanks for walking me through it.My other question was how to send the timeout value to the backend. Bruce
said at one point:Timeout can be part of BEGIN, or a SET value, which would work from
jdbc.I'm not sure how this would work. The timeout value would be sent as part
of a SQL query?
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.
--
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 19026Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
My other question was how to send the timeout value to the backend.
I would imagine that the most convenient way to handle it would be as
a SET variable:
SET query_timeout = n;
Establishes a time limit on subsequent queries (n expressed in
milliseconds, perhaps).
SET query_timeout = 0;
Disables query time limit.
This assumes that the query timeout should apply to each subsequent
query, individually, until explicitly canceled. If you want a timeout
that applies to only one query and is then forgotten, then maybe this
wouldn't be the most convenient definition. What semantics are you
trying to obtain, exactly?
regards, tom lane
On Mon, 1 Apr 2002, Tom Lane wrote:
This assumes that the query timeout should apply to each subsequent
query, individually, until explicitly canceled. If you want a timeout
that applies to only one query and is then forgotten, then maybe this
wouldn't be the most convenient definition. What semantics are you
trying to obtain, exactly?
The semantices of the JDBC API:
"Transaction::setQueryTimeout(): Sets the number of seconds the driver
will wait for a Statement to execute to the given number of seconds.
If the limit is exceeded, a SQLException is thrown."
So it should apply to all queries on a given transaction. I think that the
above implemenation suggestion (and Bruce's) would apply to all queries,
regardless of which transaction they were associated with. If each
transaction has some kind of unique ID, maybe that could be added to the
SET statement?
Does anyone know how someone else did this (mSQL, mySQL, etc)? It seems
like there ought to already exist some sort of standard. I'll poke around
and see if I can find anything.
j
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 19026Import Notes
Reply to msg id not found: Pine.LNX.4.30.0204011245590.683-100000@peter.localdomain | Resolved by subject fallback
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
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
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
Tom Lane wrote:
Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
My other question was how to send the timeout value to the backend.
I would imagine that the most convenient way to handle it would be as
a SET variable:SET query_timeout = n;
Establishes a time limit on subsequent queries (n expressed in
milliseconds, perhaps).SET query_timeout = 0;
Disables query time limit.
This assumes that the query timeout should apply to each subsequent
query, individually, until explicitly canceled. If you want a timeout
that applies to only one query and is then forgotten, then maybe this
wouldn't be the most convenient definition. What semantics are you
trying to obtain, exactly?
Why don't we use two separate GUC variables and leave the
BEGIN syntax as is completely?
SET transaction_timeout = m;
SET statement_timeout = n;
The alarm is set to the smaller of (what's left for) the
transaction or statement.
If you want to go sub-second, I suggest making it
microseconds. That's what struct timeval (used in struct
itimerval) uses. But I strongly suggest not doing so at all,
because the usage of itimers disables the ability to profile
with gprof completely. Compute the time spent so far in a
transaction exactly, but round UP to full seconds for the
alarm allways.
And before someone asks, no, I don't think that a
connection_timeout is a good thing.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
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:
Show quoted text
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)
On Mon, 1 Apr 2002, Jan Wieck wrote:
Why don't we use two separate GUC variables and leave the
BEGIN syntax as is completely?SET transaction_timeout = m;
SET statement_timeout = n;
What's a GUC variable? Would this apply to all subsequent statements? I
think it needs to apply to just the specified statement.
I'm sorry about the confusion earlier when I said that
setQueryTimeout() was transaction-level; Barry Lind correctly pointed out
that it is statement-level. We mostly seem to feel that we don't want to
do both, so is statement-only okay? Jan, do you feel strongly that you
want to see both implemented?
If you want to go sub-second, I suggest making it
microseconds. That's what struct timeval (used in struct
I don't think that's necessary. JDBC only wants it specified in seconds.
j
Jessica Perry Hekman <jphekman@dynamicdiagrams.com> writes:
What's a GUC variable?
A parameter that you can set with SET.
Would this apply to all subsequent statements? I
think it needs to apply to just the specified statement.
Yes, if the JDBC spec expects this to be applied to just a single
statement, then a SET variable doesn't fit very nicely with that.
You'd have to have logic on the application side to reset the variable
to "no limit" after the statement --- and this could be rather
difficult. (For example, if you are inside a transaction block and
the statement errors out, you won't be able to simply issue a new SET;
so you'd have to remember that you needed a SET until after you exit
the transaction block. Ugh.)
On the other hand, we do not have anything in the backend now that
applies to just one statement and then automatically resets afterwards;
and I'm not eager to add a parameter with that behavior just for JDBC's
convenience. It seems like it'd be a big wart.
regards, tom lane
