Thread: 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, Pennsylvania19026
On 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
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 > 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
> 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 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? 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, Pennsylvania19026
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?
regards, tom lane
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
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, Pennsylvania19026
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, Pennsylvania19026
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
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 driverwill wait for a Statement to execute to the given numberof 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
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
abilityto 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: > 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
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
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? >
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
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 waitfor the result). We do NOT always know in advance how patient we will be to wait for the result. Therefore I think theclient 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
> 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 Well imho that interpretation would be completely unobvious. My first guess would have been, that with this syntax the whole transaction must commit or rollback within 5 seconds. Thus I think we only need statement_timeout. ODBC, same as JDBC wants it at the statement handle level. ODBC also provides for a default that applies to all statement handles of this connection (They call the statement attr QUERY_TIMEOUT, so imho there is room for interpretation whether it applies to selects only, which I would find absurd). Andreas
On Mon, 1 Apr 2002, Tom Lane wrote: > 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. Does that leave us with implementing query timeouts in JDBC (timer in the driver; then the driver sends a cancel request to the backend)? j
Jessica Perry Hekman wrote: > On Mon, 1 Apr 2002, Tom Lane wrote: > > > 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. > > Does that leave us with implementing query timeouts in JDBC (timer in the > driver; then the driver sends a cancel request to the backend)? No, I think we have to find a way to do this in the backend; just not sure how yet. I see the problem Tom is pointing out, that SET is ignored if the transaction has already aborted:test=> begin;BEGINtest=> lkjasdf;ERROR: parser: parse error at or near "lkjasdf"test=> setserver_min_messages = 'log';WARNING: current transaction is aborted, queries ignored until end oftransaction block*ABORTSTATE*test=> so if the transaction aborted, the reset of the statement_timeout would not happen. The only way the application could code this would be with this: BEGIN WORK;query;SET statement_timeout = 4;query;SET statement_timeout = 0;query;COMMIT;SET statement_timeout = 0; Basically, it does the reset twice, once assuming the transaction doesn't abort, and another assuming it does abort. Is this something that the JDBC and ODBC drivers can do automatically? -- 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, Pennsylvania19026
On Tue, 2 Apr 2002, Bruce Momjian wrote: > BEGIN WORK; > query; > SET statement_timeout = 4; > query; > SET statement_timeout = 0; > query; > COMMIT; > SET statement_timeout = 0; > > Basically, it does the reset twice, once assuming the transaction > doesn't abort, and another assuming it does abort. Is this something > that the JDBC and ODBC drivers can do automatically? I can't speak for ODBC. Seems like in JDBC, Connection::commit() would call code clearing the timeout, and Statement::executeQuery() and executeUpdate() would do the same. j
Jessica Perry Hekman wrote: > On Tue, 2 Apr 2002, Bruce Momjian wrote: > > > BEGIN WORK; > > query; > > SET statement_timeout = 4; > > query; > > SET statement_timeout = 0; > > query; > > COMMIT; > > SET statement_timeout = 0; > > > > Basically, it does the reset twice, once assuming the transaction > > doesn't abort, and another assuming it does abort. Is this something > > that the JDBC and ODBC drivers can do automatically? > > I can't speak for ODBC. Seems like in JDBC, Connection::commit() would > call code clearing the timeout, and Statement::executeQuery() and > executeUpdate() would do the same. Well, then a SET variable would work fine for statement-level queries. Just add the part for commit/abort transaction. -- 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, Pennsylvania19026
Since both the JDBC and ODBC specs have essentially the same symantics for this, I would hope this is done in the backend instead of both interfaces. --Barry Jessica Perry Hekman wrote: > On Mon, 1 Apr 2002, Tom Lane wrote: > > >>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. > > > Does that leave us with implementing query timeouts in JDBC (timer in the > driver; then the driver sends a cancel request to the backend)? > > 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) >
Jessica Perry Hekman wrote: > On Mon, 1 Apr 2002, Tom Lane wrote: > > > 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. > > Does that leave us with implementing query timeouts in JDBC (timer in the > driver; then the driver sends a cancel request to the backend)? No, I think we have to find a way to do this in the backend; just not sure how yet. I see the problem Tom is pointing out, that SET is ignored if the transaction has already aborted:test=> begin;BEGINtest=> lkjasdf;ERROR: parser: parse error at or near "lkjasdf"test=> setserver_min_messages = 'log';WARNING: current transaction is aborted, queries ignored until end oftransaction block*ABORTSTATE*test=> so if the transaction aborted, the reset of the statement_timeout would not happen. The only way the application could code this would be with this: BEGIN WORK;query;SET statement_timeout = 4;query;SET statement_timeout = 0;query;COMMIT;SET statement_timeout = 0; Basically, it does the reset twice, once assuming the transaction doesn't abort, and another assuming it does abort. Is this something that the JDBC and ODBC drivers can do automatically? -- 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, Pennsylvania19026 --ELM1017886909-22092-0_--
On Tue, 2 Apr 2002, Barry Lind wrote: > Since both the JDBC and ODBC specs have essentially the same symantics > for this, I would hope this is done in the backend instead of both > interfaces. The current plan seems to be to make changes in the backend and the JDBC interface, the bulk of the implementation being in the backend. j
Jessica Perry Hekman wrote: > On Tue, 2 Apr 2002, Barry Lind wrote: > > > Since both the JDBC and ODBC specs have essentially the same symantics > > for this, I would hope this is done in the backend instead of both > > interfaces. > > The current plan seems to be to make changes in the backend and the JDBC > interface, the bulk of the implementation being in the backend. Yes, ODBC and JDBC need this, and I am sure psql folks will use it too, not counting libpq and all the others. We just need a way to specify statement-level SET options inside a transaction where the statement may fail and ignore the SET command that resets the timeout. We don't have any mechanism to reset the timeout parameter at the end of a transaction automatically, which would solve our problem with failed transactions. Does anyone know the ramifications of allowing SET to work in an aborted transaction? It is my understanding that SET doesn't really have transaction semantics anyway, e.g. a SET that is done in a transaction that is later aborted is still valid: test=> BEGIN;BEGINtest=> SET server_min_messages to 'debug5';SET VARIABLEtest=> ABORT;ROLLBACKtest=> SHOW server_min_messages;INFO: server_min_messages is debug5SHOW VARIABLE Having shown this, it could be argued that SET should work in an already-aborted transaction. Why should having the SET before or after the transaction is canceled have any effect. This illustrates it a little clearer:test=> BEGIN;BEGINtest=> SET server_min_messages to 'debug3';SET VARIABLEtest=> asdf; ERROR: parser: parseerror at or near "asdf"test=> SET server_min_messages to 'debug1';WARNING: current transaction is aborted, queriesignored until end oftransaction block*ABORT STATE*test=> COMMIT;COMMITtest=> SHOW server_min_messages;INFO: server_min_messagesis debug3SHOW VARIABLEtest=> Why should the 'debug3' be honored if the transaction aborted. And if it is OK that is was honored, is it OK that the 'debug1' was not honored? Allowing SET to be valid after a transaction aborts would solve our SET timeout problem. There is also a feeling that people may want to set maximum counts for transactions too because the transaction could be holding locks you want released. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Does anyone know the ramifications of allowing SET to work in an aborted
> transaction?
This is not an option.
The case that will definitely Not Work is SET variables whose setting
or checking requires database accesses. The new search_path variable
certainly works that way; not sure if there are any other cases at the
moment, but I'd not like to say that there can never be any such
variables.
regards, tom lane
Bruce Momjian wrote: > > Jessica Perry Hekman wrote: > > On Tue, 2 Apr 2002, Barry Lind wrote: > > > > > Since both the JDBC and ODBC specs have essentially the same symantics > > > for this, I would hope this is done in the backend instead of both > > > interfaces. > > > > The current plan seems to be to make changes in the backend and the JDBC > > interface, the bulk of the implementation being in the backend. > > Yes, ODBC and JDBC need this, and I am sure psql folks will use it too, > not counting libpq and all the others. I wasn't able to follow this thread sorry. ODBC has QUERY_TIMEOUT and CONNECTION_TIMEOUT. > We just need a way to specify statement-level SET options inside a > transaction where the statement may fail and ignore the SET command that > resets the timeout. We don't have any mechanism to reset the timeout > parameter at the end of a transaction automatically, Why should the timeout be reset automatically ? regards, Hiroshi Inoue
> > > The current plan seems to be to make changes in the backend and the JDBC > > > interface, the bulk of the implementation being in the backend. > > > > Yes, ODBC and JDBC need this, and I am sure psql folks will use it too, > > not counting libpq and all the others. > > I wasn't able to follow this thread sorry. > ODBC has QUERY_TIMEOUT and CONNECTION_TIMEOUT. > > > We just need a way to specify statement-level SET options inside a > > transaction where the statement may fail and ignore the SET command that > > resets the timeout. We don't have any mechanism to reset the timeout > > parameter at the end of a transaction automatically, > > Why should the timeout be reset automatically ? It doesn't need to be reset automatically, but the problem is that if you are doing a timeout for single statement in a transaction, and that statement aborts the transaction, the SET command after it to reset the timeout fails. I am attaching the email that describes the issue. -- 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 >From pgman Tue Apr 2 13:29:51 2002 Subject: Re: [HACKERS] timeout implementation issues In-Reply-To: <Pine.LNX.4.21.0204021112230.11943-100000@atalanta.dynamicdiagrams.com> To: Jessica Perry Hekman <jphekman@dynamicdiagrams.com> Date: Tue, 2 Apr 2002 13:39:30 -0500 (EST) cc: Tom Lane <tgl@sss.pgh.pa.us>, Jan Wieck <janwieck@yahoo.com>, pgsql-hackers@postgresql.org X-Mailer: ELM [version 2.4ME+ PL97 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII Content-Length: 1656 Status: OR Jessica Perry Hekman wrote: > On Mon, 1 Apr 2002, Tom Lane wrote: > > > 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. > > Does that leave us with implementing query timeouts in JDBC (timer in the > driver; then the driver sends a cancel request to the backend)? No, I think we have to find a way to do this in the backend; just not sure how yet. I see the problem Tom is pointing out, that SET is ignored if the transaction has already aborted: test=> begin; BEGIN test=> lkjasdf; ERROR: parser: parse error at or near "lkjasdf" test=> set server_min_messages = 'log'; WARNING: current transaction is aborted, queries ignored until end of transaction block *ABORT STATE* test=> so if the transaction aborted, the reset of the statement_timeout would not happen. The only way the application could code this would be with this: BEGIN WORK; query; SET statement_timeout = 4; query; SET statement_timeout = 0; query; COMMIT; SET statement_timeout = 0; Basically, it does the reset twice, once assuming the transaction doesn't abort, and another assuming it does abort. Is this something that the JDBC and ODBC drivers can do automatically? -- 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
Bruce Momjian wrote: > > > > > The current plan seems to be to make changes in the backend and the JDBC > > > > interface, the bulk of the implementation being in the backend. > > > > > > Yes, ODBC and JDBC need this, and I am sure psql folks will use it too, > > > not counting libpq and all the others. > > > > I wasn't able to follow this thread sorry. > > ODBC has QUERY_TIMEOUT and CONNECTION_TIMEOUT. > > > > > We just need a way to specify statement-level SET options inside a > > > transaction where the statement may fail and ignore the SET command that > > > resets the timeout. We don't have any mechanism to reset the timeout > > > parameter at the end of a transaction automatically, > > > > Why should the timeout be reset automatically ? > > It doesn't need to be reset automatically, but the problem is that if > you are doing a timeout for single statement in a transaction, and that > statement aborts the transaction, the SET command after it to reset the > timeout fails. As for ODBC, there's no state that *abort* but still inside a transaction currently. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > Why should the timeout be reset automatically ? > > > > It doesn't need to be reset automatically, but the problem is that if > > you are doing a timeout for single statement in a transaction, and that > > statement aborts the transaction, the SET command after it to reset the > > timeout fails. > > As for ODBC, there's no state that *abort* but still inside > a transaction currently. Yes, the strange thing is that SET inside a transaction _after_ the transaction aborts is ignored, while SET before inside a transaction before the transaction aborts is accepted. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > > Why should the timeout be reset automatically ? > > > > > > It doesn't need to be reset automatically, but the problem is that if > > > you are doing a timeout for single statement in a transaction, and that > > > statement aborts the transaction, the SET command after it to reset the > > > timeout fails. > > > > As for ODBC, there's no state that *abort* but still inside > > a transaction currently. > > Yes, the strange thing is that SET inside a transaction _after_ the > transaction aborts is ignored, while SET before inside a transaction > before the transaction aborts is accepted. What I meant is there's no such problem with psqlodbc at least currently because the driver issues ROLLBACK automatically on abort inside a transaction. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Hiroshi Inoue wrote: > > > > > Why should the timeout be reset automatically ? > > > > > > > > It doesn't need to be reset automatically, but the problem is that if > > > > you are doing a timeout for single statement in a transaction, and that > > > > statement aborts the transaction, the SET command after it to reset the > > > > timeout fails. > > > > > > As for ODBC, there's no state that *abort* but still inside > > > a transaction currently. > > > > Yes, the strange thing is that SET inside a transaction _after_ the > > transaction aborts is ignored, while SET before inside a transaction > > before the transaction aborts is accepted. > > What I meant is there's no such problem with psqlodbc > at least currently because the driver issues ROLLBACK > automatically on abort inside a transaction. If it does that, what happens with the rest of the queries in a transaction? Do they get executed in their own transactions, or are they somehow ignored. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > Hiroshi Inoue wrote: > > > > > > Why should the timeout be reset automatically ? > > > > > > > > > > It doesn't need to be reset automatically, but the problem is that if > > > > > you are doing a timeout for single statement in a transaction, and that > > > > > statement aborts the transaction, the SET command after it to reset the > > > > > timeout fails. > > > > > > > > As for ODBC, there's no state that *abort* but still inside > > > > a transaction currently. > > > > > > Yes, the strange thing is that SET inside a transaction _after_ the > > > transaction aborts is ignored, while SET before inside a transaction > > > before the transaction aborts is accepted. > > > > What I meant is there's no such problem with psqlodbc > > at least currently because the driver issues ROLLBACK > > automatically on abort inside a transaction. > > If it does that, what happens with the rest of the queries in a > transaction? Do they get executed in their own transactions, or are > they somehow ignored. They would be executed in a new transaction. Queries shouldn't be issued blindly(without error checking). regards, Hiroshi Inoue
OK, I have a few ideas on this and I think one of them will have to be implemented. Basically, we have this SET problem with all our variables, e.g. if you SET explain_pretty_print or enable_seqscan in a multi-statement transaction, and the transaction aborts after the variable is turned on but before the variable is turned off, it will remain on for the remainder of the session. See the attached email for an example. It shows this problem with timeout, but all the SET variables have this issue. I think we have only a few options:o Allow SET to execute even if the transaction is in ABORT state (Tom says some SETvariables need db access and will fail.)o If a SET is performed while in transaction ABORT state, queue up the SETcommands to run after the transaction completeso Issue a RESET on transaction completion (commit or abort) for any SET variable set in the transaction. (This will cause problems for API's like ecpg which are always in a transaction.)o Issue a variable RESET on transaction ABORT for any SET variable modified by a transaction. I think the last one is the most reasonable option. --------------------------------------------------------------------------- Bruce Momjian wrote: > Jessica Perry Hekman wrote: > > On Tue, 2 Apr 2002, Barry Lind wrote: > > > > > Since both the JDBC and ODBC specs have essentially the same symantics > > > for this, I would hope this is done in the backend instead of both > > > interfaces. > > > > The current plan seems to be to make changes in the backend and the JDBC > > interface, the bulk of the implementation being in the backend. > > Yes, ODBC and JDBC need this, and I am sure psql folks will use it too, > not counting libpq and all the others. > > We just need a way to specify statement-level SET options inside a > transaction where the statement may fail and ignore the SET command that > resets the timeout. We don't have any mechanism to reset the timeout > parameter at the end of a transaction automatically, which would solve > our problem with failed transactions. > > Does anyone know the ramifications of allowing SET to work in an aborted > transaction? It is my understanding that SET doesn't really have > transaction semantics anyway, e.g. a SET that is done in a transaction > that is later aborted is still valid: > > test=> BEGIN; > BEGIN > test=> SET server_min_messages to 'debug5'; > SET VARIABLE > test=> ABORT; > ROLLBACK > test=> SHOW server_min_messages; > INFO: server_min_messages is debug5 > SHOW VARIABLE > > Having shown this, it could be argued that SET should work in an > already-aborted transaction. Why should having the SET before or after > the transaction is canceled have any effect. This illustrates it a > little clearer: > > test=> BEGIN; > BEGIN > test=> SET server_min_messages to 'debug3'; > SET VARIABLE > test=> asdf; > ERROR: parser: parse error at or near "asdf" > test=> SET server_min_messages to 'debug1'; > WARNING: current transaction is aborted, queries ignored until end of > transaction block > *ABORT STATE* > test=> COMMIT; > COMMIT > test=> SHOW server_min_messages; > INFO: server_min_messages is debug3 > SHOW VARIABLE > test=> > > Why should the 'debug3' be honored if the transaction aborted. And if > it is OK that is was honored, is it OK that the 'debug1' was not > honored? > > Allowing SET to be valid after a transaction aborts would solve our SET > timeout problem. > > There is also a feeling that people may want to set maximum counts for > transactions too because the transaction could be holding locks you want > released. > > -- > 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 > > ---------------------------(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 > -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think we have only a few options:
You forgot
o Do nothing.
IMHO the current behavior is not broken, and does not need fixed.
All of the options you suggest are surely more broken than the current
behavior.
> o Issue a RESET on transaction completion (commit or abort) for any
> SET variable set in the transaction. (This will cause problems
> for API's like ecpg which are always in a transaction.)
RESET would certainly not be a desirable behavior. If we want SET vars
to roll back on abort, then they should roll back --- ie, resume their
transaction-start-time values. But I doubt it's worth the trouble.
That behavior would do nothing to help JDBC implement timeouts, since
they'd still need to change the value again explicitly after successful
transaction completion.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I think we have only a few options: > > You forgot > > o Do nothing. > > IMHO the current behavior is not broken, and does not need fixed. > All of the options you suggest are surely more broken than the current > behavior. I think it is broken. What logic is there that SET before transaction abort is performed, but after abort it is ignored? What if someone wants a specific optimizer parameter for a statement in a transaction, like geqo_* or enable_seqscan off, and they perform the SET before the statement OK but if the statement fails, the SET after it is ignored. That doesn't seem like very normal behavior to me. We are seeing this in the timeout case, but in fact the other SET commands when run in a transaction have the same problem. > > o Issue a RESET on transaction completion (commit or abort) for any > > SET variable set in the transaction. (This will cause problems > > for API's like ecpg which are always in a transaction.) > > RESET would certainly not be a desirable behavior. If we want SET vars > to roll back on abort, then they should roll back --- ie, resume their > transaction-start-time values. But I doubt it's worth the trouble. > That behavior would do nothing to help JDBC implement timeouts, since > they'd still need to change the value again explicitly after successful > transaction completion. Yes, I now think that saving the SET commands that are ignored in a transaction and running them _after_ the transaction completes may be the best thing. They can be stored as C strings in a stable memory context and just run on transaction completion. If we don't somehow get this to work, how do we do timeouts, which we all know we should have? -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, I now think that saving the SET commands that are ignored in a
> transaction and running them _after_ the transaction completes may be
> the best thing.
No, that's just plain ridiculous. If you want to change the semantics
of SET, then make it work *correctly*, viz like an SQL statement: roll
it back on transaction abort. Otherwise leave it alone.
> If we don't somehow get this to work, how do we do timeouts, which we
> all know we should have?
This is utterly unrelated to timeouts. With or without any changes in
SET behavior, JDBC would need to issue a SET after completion of the
transaction if they wanted to revert a query_timeout variable to the
no-timeout state.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Yes, I now think that saving the SET commands that are ignored in a > > transaction and running them _after_ the transaction completes may be > > the best thing. > > No, that's just plain ridiculous. If you want to change the semantics No more ridiculous than what we have now. > of SET, then make it work *correctly*, viz like an SQL statement: roll > it back on transaction abort. Otherwise leave it alone. I am not going to leave it alone based only on your say-so, Tom. > > If we don't somehow get this to work, how do we do timeouts, which we > > all know we should have? > > This is utterly unrelated to timeouts. With or without any changes in > SET behavior, JDBC would need to issue a SET after completion of the > transaction if they wanted to revert a query_timeout variable to the > no-timeout state. "Utterly unrelated?" No. If we can get SET to work properly in transactions, jdbc can cleanly issue SET timeout=4, statement, SET timeout=0. Without it, using SET for timeout is a problem. That's how we got to this issue in the first place. I am still looking for a constructive idea on how we can get this to work, rather than calling my ideas "ridiculous". -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am still looking for a constructive idea on how we can get this to
> work, rather than calling my ideas "ridiculous".
We know very well how to make it work: JDBC can issue a SET timeout = 0
after exiting the transaction. You're proposing to change the semantics
of SET into something quite bizarre in order to allow JDBC to not have
to work as hard. I think that's a bad tradeoff.
regards, tom lane
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I am still looking for a constructive idea on how we can get this to > > work, rather than calling my ideas "ridiculous". > > We know very well how to make it work: JDBC can issue a SET timeout = 0 > after exiting the transaction. You're proposing to change the semantics > of SET into something quite bizarre in order to allow JDBC to not have > to work as hard. I think that's a bad tradeoff. Or we don't have to reset the timeout at all. For example when we are about to issue a command, we can check if the requested timeout is different from the current server's timeout. We don't have to (re)set the timeout unless they are different. regards, Hiroshi Inoue
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I am still looking for a constructive idea on how we can get this to > > work, rather than calling my ideas "ridiculous". > > We know very well how to make it work: JDBC can issue a SET timeout = 0 > after exiting the transaction. You're proposing to change the semantics > of SET into something quite bizarre in order to allow JDBC to not have > to work as hard. I think that's a bad tradeoff. It that acceptable to the JDBC folks? It requires two "SET timeout = 0" statements, one after the statement in the transaction, and another after the transaction COMMIT WORK. -- 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, Pennsylvania19026
Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Yes, I now think that saving the SET commands that are ignored in a
> > > transaction and running them _after_ the transaction completes may be
> > > the best thing.
> >
> > No, that's just plain ridiculous. If you want to change the semantics
>
> No more ridiculous than what we have now.
>
> > of SET, then make it work *correctly*, viz like an SQL statement: roll
> > it back on transaction abort. Otherwise leave it alone.
>
> I am not going to leave it alone based only on your say-so, Tom.
I have to agree with Tom here. It's not right to hack up SET to be accepted in transaction abort state. Nor is it
rightto queue up SET requests then. If those queued SET's lead to errors, when do you report them? On
ROLLBACK?
If at all, SET commands should behave like everything else. If done inside a transaction, they have to
rollback.
> > > If we don't somehow get this to work, how do we do timeouts, which we
> > > all know we should have?
> >
> > This is utterly unrelated to timeouts. With or without any changes in
> > SET behavior, JDBC would need to issue a SET after completion of the
> > transaction if they wanted to revert a query_timeout variable to the
> > no-timeout state.
>
> "Utterly unrelated?" No. If we can get SET to work properly in
> transactions, jdbc can cleanly issue SET timeout=4, statement, SET
> timeout=0. Without it, using SET for timeout is a problem. That's how
> we got to this issue in the first place.
Could we get out of this by defining that "timeout" is automatically reset at next statement end? So that
theentire thing is
SET timeout=4; SELECT ...; -- We're back in no-timeout
And that it doesn't matter if we're in a transaction, if the statement aborts, yadda yadda...
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Thu, 4 Apr 2002, Bruce Momjian wrote: > It that acceptable to the JDBC folks? It requires two "SET timeout = 0" > statements, one after the statement in the transaction, and another > after the transaction COMMIT WORK. That's fine, though probably about as much work as just implementing the whole thing in JDBC. j
Jan Wieck <janwieck@yahoo.com> writes:
> Could we get out of this by defining that "timeout" is
> automatically reset at next statement end?
I was hoping to avoid that, because it seems like a wart. OTOH,
it'd be less of a wart than the global changes of semantics that
Bruce is proposing :-(
How exactly would you make this happen? The simplest way I can think of
to do it (reset timeout in outer loop in postgres.c) would not work,
because it'd reset the timeout as soon as the SET statement completes.
How would you get the setting to survive for exactly one additional
statement?
regards, tom lane
On Fri, Apr 05, 2002 at 11:19:04AM -0500, Tom Lane wrote: > Jan Wieck <janwieck@yahoo.com> writes: > > Could we get out of this by defining that "timeout" is > > automatically reset at next statement end? > > I was hoping to avoid that, because it seems like a wart. OTOH, > it'd be less of a wart than the global changes of semantics that > Bruce is proposing :-( > > How exactly would you make this happen? The simplest way I can think of > to do it (reset timeout in outer loop in postgres.c) would not work, > because it'd reset the timeout as soon as the SET statement completes. > How would you get the setting to survive for exactly one additional > statement? How about not messing with the SET, but adding it to the SELECT syntax itself? a "WITH TIMEOUT" clause? This is the first of the (proposed) SET variables that affects query performance that is not a 'twiddle with the internals because something is really wrong' hack (or debugging tool, if you will) Argueably, those also suffer from the punching through the transaction problem: I'd certainly hate (for example) to have sequential scans disabled for an entire connection because one gnarly query that the optimizer guesses wrong on died, and my reset got ignored. I'd hate it, but understand that it's a crufty hack to get around a problem, and just deal with resetting the transaction/connection. Timeouts, on the other hand, are a much more respectable mainline sort of extension, apparently required for certain standards (The JDBC people started this discussion, right?). They should be fully supported by the transactional machinery, however that is decided. If that means all SETs become transactional, I don't really see a problem with that. Or, as I suggested above, extend the SELECT (and other querys?) syntax seems reasonable. More so than the non-standard 'use this index, really' types of extensions that other RDBMSs provide, that we've rightly avoided. Thoughts? Ross
Tom Lane wrote: > Jan Wieck <janwieck@yahoo.com> writes: > > Could we get out of this by defining that "timeout" is > > automatically reset at next statement end? > > I was hoping to avoid that, because it seems like a wart. OTOH, > it'd be less of a wart than the global changes of semantics that > Bruce is proposing :-( > > How exactly would you make this happen? The simplest way I can think of > to do it (reset timeout in outer loop in postgres.c) would not work, > because it'd reset the timeout as soon as the SET statement completes. > How would you get the setting to survive for exactly one additional > statement? I would vote for a general callback registering mechanism, where you can specify an event, a function and an opaque pointer. Possible events then would be end of statement, end of transaction, commit, abort, regular end ofsession. Sure, it looks like total overkill for this minor JDBC problem. But I like general support structures tobe in place early. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <janwieck@yahoo.com> writes:
> If at all, SET commands should behave like everything else.
> If done inside a transaction, they have to rollback.
I have thought of a scenario that may be sufficient to justify fixing
SETs to roll back on transaction abort. Consider
BEGIN;
CREATE SCHEMA foo;
SET search_path = 'foo, public';
ROLLBACK;
As the code stands, this will leave you with an invalid search path.
(What's worse, if you now execute CREATE TABLE, it will happily create
tables belonging to the vanished namespace foo. Everything will seem
to work fine ... until you try to find those tables again in a new
session ...)
It seems clear to me that SET *should* roll back on abort. Just a
matter of how important is it to fix.
regards, tom lane
Ross J. Reedstrom wrote: > On Fri, Apr 05, 2002 at 11:19:04AM -0500, Tom Lane wrote: > > Jan Wieck <janwieck@yahoo.com> writes: > > > Could we get out of this by defining that "timeout" is > > > automatically reset at next statement end? > > > > I was hoping to avoid that, because it seems like a wart. OTOH, > > it'd be less of a wart than the global changes of semantics that > > Bruce is proposing :-( > > > > How exactly would you make this happen? The simplest way I can think of > > to do it (reset timeout in outer loop in postgres.c) would not work, > > because it'd reset the timeout as soon as the SET statement completes. > > How would you get the setting to survive for exactly one additional > > statement? > > How about not messing with the SET, but adding it to the SELECT syntax > itself? a "WITH TIMEOUT" clause? Only SELECT? I thought all DML-statements should honour it. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> Or, as I suggested above, extend the SELECT (and other querys?) syntax > seems reasonable. More so than the non-standard 'use this index, really' > types of extensions that other RDBMSs provide, that we've rightly avoided. I think we need timeout for all statement. -- 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, Pennsylvania19026
Tom Lane wrote: > Jan Wieck <janwieck@yahoo.com> writes: > > Could we get out of this by defining that "timeout" is > > automatically reset at next statement end? > > I was hoping to avoid that, because it seems like a wart. OTOH, > it'd be less of a wart than the global changes of semantics that > Bruce is proposing :-( > > How exactly would you make this happen? The simplest way I can think of > to do it (reset timeout in outer loop in postgres.c) would not work, > because it'd reset the timeout as soon as the SET statement completes. > How would you get the setting to survive for exactly one additional > statement? Sure, you could reset it, but there are going to be cases where you want to do a timeout=6000 for the entire session. If it resets after the first statement, this is hard to do. -- 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, Pennsylvania19026
Tom Lane wrote: > Jan Wieck <janwieck@yahoo.com> writes: > > If at all, SET commands should behave like everything else. > > If done inside a transaction, they have to rollback. > > I have thought of a scenario that may be sufficient to justify fixing > SETs to roll back on transaction abort. Consider > > BEGIN; > > CREATE SCHEMA foo; > > SET search_path = 'foo, public'; > > ROLLBACK; > > As the code stands, this will leave you with an invalid search path. > (What's worse, if you now execute CREATE TABLE, it will happily create > tables belonging to the vanished namespace foo. Everything will seem > to work fine ... until you try to find those tables again in a new > session ...) > > It seems clear to me that SET *should* roll back on abort. Just a > matter of how important is it to fix. That was my point, that having SET work pre-abort and ignored post-abort is broken itself, whether we implement timeout or not. Before we had tuple-reading SET variables, it probably didn't matter, but now with schemas, I can see it is more of an issue. -- 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, Pennsylvania19026
> -----Original Message----- > From: Tom Lane > > Jan Wieck <janwieck@yahoo.com> writes: > > Could we get out of this by defining that "timeout" is > > automatically reset at next statement end? > > I was hoping to avoid that, because it seems like a wart. OTOH, > it'd be less of a wart than the global changes of semantics that > Bruce is proposing :-( Probably I'm misunderstanding this thread. Why must the query_timeout be reset particularly ? What's wrong with simply issueing set query_timeout command just before every query ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > -----Original Message----- > > From: Tom Lane > > > > Jan Wieck <janwieck@yahoo.com> writes: > > > Could we get out of this by defining that "timeout" is > > > automatically reset at next statement end? > > > > I was hoping to avoid that, because it seems like a wart. OTOH, > > it'd be less of a wart than the global changes of semantics that > > Bruce is proposing :-( > > Probably I'm misunderstanding this thread. > Why must the query_timeout be reset particularly ? > What's wrong with simply issueing set query_timeout > command just before every query ? You could do that, but we also imagine cases where people would want to set a timeout for each query in an entire session. -- 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, Pennsylvania19026
On Sat, 6 Apr 2002, Bruce Momjian wrote: > > What's wrong with simply issueing set query_timeout > > command just before every query ? > > You could do that, but we also imagine cases where people would want to > set a timeout for each query in an entire session. One approach might be for the interface to take care of setting the query timeout before each query, and just ask the backend to handle timeouts per-query. So from the user's perspective, session-level timeouts would exist, but the backend would not have to worry about rolling back timeouts. j
Jessica Perry Hekman wrote: > On Sat, 6 Apr 2002, Bruce Momjian wrote: > > > > What's wrong with simply issueing set query_timeout > > > command just before every query ? > > > > You could do that, but we also imagine cases where people would want to > > set a timeout for each query in an entire session. > > One approach might be for the interface to take care of setting the query > timeout before each query, and just ask the backend to handle timeouts > per-query. So from the user's perspective, session-level timeouts would > exist, but the backend would not have to worry about rolling back > timeouts. Yes, that would work, but libpq and psql would have trouble doing full session timeouts. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> One approach might be for the interface to take care of setting the query
>> timeout before each query, and just ask the backend to handle timeouts
>> per-query. So from the user's perspective, session-level timeouts would
>> exist, but the backend would not have to worry about rolling back
>> timeouts.
> Yes, that would work, but libpq and psql would have trouble doing full
> session timeouts.
From the backend's perspective it'd be a *lot* cleaner to support
persistent timeouts (good 'til canceled) than one-shots. If that's
the choice then let's let the frontend library worry about implementing
one-shots.
Note: I am now pretty well convinced that we *must* fix SET to roll back
to start-of-transaction settings on transaction abort. If we do that,
at least some of the difficulty disappears for JDBC to handle one-shot
timeouts by issuing SETs before and after the target query against a
query_timeout variable that otherwise acts like a good-til-canceled
setting. Can we all compromise on that?
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> One approach might be for the interface to take care of setting the query > >> timeout before each query, and just ask the backend to handle timeouts > >> per-query. So from the user's perspective, session-level timeouts would > >> exist, but the backend would not have to worry about rolling back > >> timeouts. > > > Yes, that would work, but libpq and psql would have trouble doing full > > session timeouts. > > >From the backend's perspective it'd be a *lot* cleaner to support > persistent timeouts (good 'til canceled) than one-shots. If that's > the choice then let's let the frontend library worry about implementing > one-shots. > > Note: I am now pretty well convinced that we *must* fix SET to roll back > to start-of-transaction settings on transaction abort. If we do that, > at least some of the difficulty disappears for JDBC to handle one-shot > timeouts by issuing SETs before and after the target query against a > query_timeout variable that otherwise acts like a good-til-canceled > setting. Can we all compromise on that? Added to TODO: * Abort SET changes made in aborted transactions We do have on_shmem_exit and on_proc_exit function call queues. Seems we will need SET to create a queue of function calls containing previous values of variables SEt in multi-statement transactions. If we execute the queue in last-in-first-out order, the variables will be restored properly. -- 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, Pennsylvania19026
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > We do have on_shmem_exit and on_proc_exit function call queues. Seems > > we will need SET to create a queue of function calls containing previous > > values of variables SEt in multi-statement transactions. If we execute > > the queue in last-in-first-out order, the variables will be restored > > properly. > > That's most certainly the hard way. I was planning to just make GUC > save a spare copy of the start-of-transaction value of each variable. Ewe, I was hoping for something with zero overhead for the non-SET case. Can we trigger the save for the first SET in the transaction? -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We do have on_shmem_exit and on_proc_exit function call queues. Seems
> we will need SET to create a queue of function calls containing previous
> values of variables SEt in multi-statement transactions. If we execute
> the queue in last-in-first-out order, the variables will be restored
> properly.
That's most certainly the hard way. I was planning to just make GUC
save a spare copy of the start-of-transaction value of each variable.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Ewe, I was hoping for something with zero overhead for the non-SET case.
Well, a function call and immediate return if no SET has been executed
in the current xact seems low enough overhead to me. We'll just keep
a flag showing whether there's anything to do.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Ewe, I was hoping for something with zero overhead for the non-SET case. > > Well, a function call and immediate return if no SET has been executed > in the current xact seems low enough overhead to me. We'll just keep > a flag showing whether there's anything to do. Oh, I thought you were going to save all the GUC variables on transaction start. I now assume you are going to have one field per variable for the pre-xact value. That is fine. -- 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, Pennsylvania19026
Tom Lane wrote: > Note: I am now pretty well convinced that we *must* fix SET to roll back > to start-of-transaction settings on transaction abort. If we do that, > at least some of the difficulty disappears for JDBC to handle one-shot > timeouts by issuing SETs before and after the target query against a > query_timeout variable that otherwise acts like a good-til-canceled > setting. Can we all compromise on that? > This plan should work well for JDBC. (It actually makes the code on the jdbc side pretty easy). thanks, --Barry
Tom Lane writes: > Note: I am now pretty well convinced that we *must* fix SET to roll back > to start-of-transaction settings on transaction abort. If we do that, > at least some of the difficulty disappears for JDBC to handle one-shot > timeouts by issuing SETs before and after the target query against a > query_timeout variable that otherwise acts like a good-til-canceled > setting. Can we all compromise on that? No. I agree that there may be some variables that must be rolled back, or where automatic reset on transaction end may be desirable (note that these are two different things), but for some variables it's completely nonsensical. Those variables describe session characteristics, not database state. For instance, time zone, default_transaction_isolation. Or consider you're raising the debug level, but it gets reset during commit so you can't debug the commit process. Or in the future we may have some SQL-compatible always-in-transaction mode which would mean that you could never set any variable to last. If you want something that's transaction-specific, invent a new mechanism. Hook in the set transaction isolation level command while you're at it. But don't break everything that's worked so far. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
>> Can we all compromise on that?
> No.
Oh dear...
> I agree that there may be some variables that must be rolled back, or
> where automatic reset on transaction end may be desirable (note that these
> are two different things), but for some variables it's completely
> nonsensical. Those variables describe session characteristics, not
> database state. For instance, time zone, default_transaction_isolation.
Uh, why? I do not see why it's unreasonable forBEGIN;SET time_zone = whatever;ROLLBACK;
to be a no-op. The fact that we haven't done that historically doesn't
count for much (unless your argument is "backwards compatibility" ...
but you didn't say that). Not long ago we couldn't roll back a DROP
TABLE command; but that didn't make it right.
> Or consider you're raising the debug level, but it gets reset during
> commit so you can't debug the commit process.
It wouldn't get reset during commit, so I assume you really meant you
wanted to debug an abort problem. But even there, what's the problem?
Set the variable *before* you enter the transaction that will abort.
> Or in the future we may
> have some SQL-compatible always-in-transaction mode which would mean that
> you could never set any variable to last.
Only if this mode prevents you from ever committing anything. Somehow
I doubt that that's either SQL-compatible or useful.
> If you want something that's transaction-specific, invent a new mechanism.
I didn't say "transaction specific". I said that if you do a SET inside
a transaction block, and then the transaction is aborted, the effects of
the SET ought to roll back along with everything else you did inside
that transaction block. I'm not seeing what the argument is against
this.
regards, tom lane
Tom Lane writes: > I didn't say "transaction specific". I said that if you do a SET inside > a transaction block, and then the transaction is aborted, the effects of > the SET ought to roll back along with everything else you did inside > that transaction block. I'm not seeing what the argument is against > this. I consider SET variables metadata that are not affected by transactions. I should be able to change my mind about my session preferences in the middle of a transaction, no matter what happens to the data in it. Say somewhere in the middle of a long transaction I think, "I should really be logging this stuff". I turn a knob to do so, and the next command fails. Is the failure logged? In which order does the rollback happen? What if I want to continue logging? If anything were to change I would like to continue accepting SET commands after an error. Of course, I would like to continue accepting any command after an error, but that's a different debate. I guess it's a matter of definition: Do you consider SET variables database state or session metadata? I think some are this and some are that. I'm not sure how to draw the line, but throwing everything from one category into the other isn't my favorite solution. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> I consider SET variables metadata that are not affected by transactions.
Why? Again, the fact that historically they've not acted that way isn't
sufficient reason for me.
> I should be able to change my mind about my session preferences in the
> middle of a transaction, no matter what happens to the data in it. Say
> somewhere in the middle of a long transaction I think, "I should really be
> logging this stuff". I turn a knob to do so, and the next command fails.
> Is the failure logged? In which order does the rollback happen? What if
> I want to continue logging?
Hm. That's a slightly more interesting example than before ... but it
comes close to arguing that logging should be under transaction control.
Surely you'd not argue that a failed transaction should erase all its
entries from the postmaster log? Why would you expect changes in log
levels to be retroactive?
> I guess it's a matter of definition: Do you consider SET variables
> database state or session metadata? I think some are this and some are
> that. I'm not sure how to draw the line, but throwing everything from one
> category into the other isn't my favorite solution.
You seem to be suggesting that we should make a variable-by-variable
decision about whether SET variables roll back on ABORT or not. I think
that way madness lies; we could spend forever debating which vars are
which, and then who will remember without consulting the documentation?
I feel we should just do it. Yeah, there might be some corner cases
where it's not the ideal behavior; but you haven't convinced me that
there are more cases where it's bad than where it's good. You sure
haven't convinced me that it's worth making SET's behavior
nigh-unpredictable-without-a-manual, which is what per-variable behavior
would be.
regards, tom lane
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > Hiroshi Inoue wrote: > > > -----Original Message----- > > > From: Tom Lane > > > > > > Jan Wieck <janwieck@yahoo.com> writes: > > > > Could we get out of this by defining that "timeout" is > > > > automatically reset at next statement end? > > > > > > I was hoping to avoid that, because it seems like a wart. OTOH, > > > it'd be less of a wart than the global changes of semantics that > > > Bruce is proposing :-( > > > > Probably I'm misunderstanding this thread. > > Why must the query_timeout be reset particularly ? > > What's wrong with simply issueing set query_timeout > > command just before every query ? > > You could do that, but we also imagine cases where people would want to > set a timeout for each query in an entire session. Sorry I couldn't understand your point. It seems the simplest and the most certain way is to call 'SET QUERY_TIMEOUT per query. The way dosen't require RESET at all. Is the overhead an issue ? regards, Hiroshi Inoue
> -----Original Message----- > From: Peter Eisentraut [mailto:peter_e@gmx.net] > > > I guess it's a matter of definition: Do you consider SET variables > database state or session metadata? Session metadata IMHO. If there are(would be) database state variables we should introduce another command for them. For example I don't think QUERY_TIMEOUT is such a variable. As I mentioned many times we can set QUERY_TIMEOUT before each query. If the overhead is an issue we can keep track of the varaible and reduce the command calls to minimum easily. regards, Hiroshi Inoue
> > > Probably I'm misunderstanding this thread. > > > Why must the query_timeout be reset particularly ? > > > What's wrong with simply issueing set query_timeout > > > command just before every query ? > > > > You could do that, but we also imagine cases where people would want to > > set a timeout for each query in an entire session. > > Sorry I couldn't understand your point. > It seems the simplest and the most certain way is to call > 'SET QUERY_TIMEOUT per query. The way dosen't require > RESET at all. Is the overhead an issue ? What about psql and libpq. Doing a timeout before every query is a pain. I realize it can be done easily in ODBC and JDBC, but we need a general timeout mechanism. -- 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, Pennsylvania19026
> > I guess it's a matter of definition: Do you consider SET variables > > database state or session metadata? I think some are this and some are > > that. I'm not sure how to draw the line, but throwing everything from one > > category into the other isn't my favorite solution. > > You seem to be suggesting that we should make a variable-by-variable > decision about whether SET variables roll back on ABORT or not. I think > that way madness lies; we could spend forever debating which vars are > which, and then who will remember without consulting the documentation? > > I feel we should just do it. Yeah, there might be some corner cases > where it's not the ideal behavior; but you haven't convinced me that > there are more cases where it's bad than where it's good. You sure > haven't convinced me that it's worth making SET's behavior > nigh-unpredictable-without-a-manual, which is what per-variable behavior > would be. I am with Tom on this one. (Nice to see he is now arguing on my side.) Making different variables behave differently is clearly going to confuse users. The argument that we should allow SET to work when the transaction is in ABORT state seems very wierd to me because we ignore every other command in that state. I think reversing out any SET's done in an aborted transaction is the clear way to go. If users want their SET to not be affected by the transaction abort, they should put their SET's outside a transaction; seems pretty clear to me. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > > > > Probably I'm misunderstanding this thread. > > > > Why must the query_timeout be reset particularly ? > > > > What's wrong with simply issueing set query_timeout > > > > command just before every query ? > > > > > > You could do that, but we also imagine cases where people would want to > > > set a timeout for each query in an entire session. > > > > Sorry I couldn't understand your point. > > It seems the simplest and the most certain way is to call > > 'SET QUERY_TIMEOUT per query. The way dosen't require > > RESET at all. Is the overhead an issue ? > > What about psql and libpq. Doing a timeout before every query is a > pain. Psql and libpq would simply issue the query according to the user's request as they currently do. What's pain with it ? regards, Hiroshi Inoue
> > > Sorry I couldn't understand your point. > > > It seems the simplest and the most certain way is to call > > > 'SET QUERY_TIMEOUT per query. The way dosen't require > > > RESET at all. Is the overhead an issue ? > > > > What about psql and libpq. Doing a timeout before every query is a > > pain. > > Psql and libpq would simply issue the query according to the > user's request as they currently do. What's pain with it ? If they wanted to place a timeout on all queries in a session, they would need a SET for every query, which seems like a pain. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > > > I guess it's a matter of definition: Do you consider SET variables > > > database state or session metadata? I think some are this and some are > > > that. I'm not sure how to draw the line, but throwing everything from one > > > category into the other isn't my favorite solution. > > > > You seem to be suggesting that we should make a variable-by-variable > > decision about whether SET variables roll back on ABORT or not. I think > > that way madness lies; we could spend forever debating which vars are > > which, and then who will remember without consulting the documentation? > > > > I feel we should just do it. Yeah, there might be some corner cases > > where it's not the ideal behavior; but you haven't convinced me that > > there are more cases where it's bad than where it's good. You sure > > haven't convinced me that it's worth making SET's behavior > > nigh-unpredictable-without-a-manual, which is what per-variable behavior > > would be. > > I am with Tom on this one. (Nice to see he is now arguing on my side.) I vote against you. If a variable is local to the session, you can change it as you like without bothering any other user(session). Automatic resetting of the varibales is rather confusing to me. regards, Hiroshi Inoue
Bruce Momjian wrote: > > > > > Sorry I couldn't understand your point. > > > > It seems the simplest and the most certain way is to call > > > > 'SET QUERY_TIMEOUT per query. The way dosen't require > > > > RESET at all. Is the overhead an issue ? > > > > > > What about psql and libpq. Doing a timeout before every query is a > > > pain. > > > > Psql and libpq would simply issue the query according to the > > user's request as they currently do. What's pain with it ? > > If they wanted to place a timeout on all queries in a session, they > would need a SET for every query, which seems like a pain. Oh I see. You mean users' pain ? If a user wants to place a timeout on all the query, he would issue SET query_timeout command only once. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > > > I guess it's a matter of definition: Do you consider SET variables > > > > database state or session metadata? I think some are this and some are > > > > that. I'm not sure how to draw the line, but throwing everything from one > > > > category into the other isn't my favorite solution. > > > > > > You seem to be suggesting that we should make a variable-by-variable > > > decision about whether SET variables roll back on ABORT or not. I think > > > that way madness lies; we could spend forever debating which vars are > > > which, and then who will remember without consulting the documentation? > > > > > > I feel we should just do it. Yeah, there might be some corner cases > > > where it's not the ideal behavior; but you haven't convinced me that > > > there are more cases where it's bad than where it's good. You sure > > > haven't convinced me that it's worth making SET's behavior > > > nigh-unpredictable-without-a-manual, which is what per-variable behavior > > > would be. > > > > I am with Tom on this one. (Nice to see he is now arguing on my side.) > > I vote against you. If a variable is local to the session, you > can change it as you like without bothering any other user(session). > Automatic resetting of the varibales is rather confusing to me. I don't see how this relates to other users. All SET commands that can be changed in psql are per backend, as far as I remember. -- 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, Pennsylvania19026
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > > > > Sorry I couldn't understand your point. > > > > > It seems the simplest and the most certain way is to call > > > > > 'SET QUERY_TIMEOUT per query. The way dosen't require > > > > > RESET at all. Is the overhead an issue ? > > > > > > > > What about psql and libpq. Doing a timeout before every query is a > > > > pain. > > > > > > Psql and libpq would simply issue the query according to the > > > user's request as they currently do. What's pain with it ? > > > > If they wanted to place a timeout on all queries in a session, they > > would need a SET for every query, which seems like a pain. > > Oh I see. You mean users' pain ? Sorry I was unclear. > If a user wants to place a timeout on all the query, he > would issue SET query_timeout command only once. I am confused. Above you state you want SET QUERY_TIMEOUT to be per-query. I assume you mean that the timeout applies for only the next query and is turned off after that. If you do that, it is hard to set a maximum duration for all queries in your session, especially in psql or libpq. Also, I am not saying that the timeout is for the entire session, but that the timeout makes sure that any query in the session that takes longer than X milliseconds is automatically cancelled. Please reply and let me know what you think. I am sure I am missing something in your comments. -- 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, Pennsylvania19026
Bruce Momjian wrote:<br /><blockquote cite="mid200204080317.g383Hj511314@candle.pha.pa.us" type="cite"><pre wrap="">HiroshiInoue wrote:<br /></pre><blockquote type="cite"><pre wrap="">Bruce Momjian wrote:<br /></pre><blockquote type="cite"><blockquotetype="cite"><blockquote type="cite"><pre wrap="">I guess it's a matter of definition: Do you considerSET variables<br />database state or session metadata? I think some are this and some are<br />that. I'm not surehow to draw the line, but throwing everything from one<br />category into the other isn't my favorite solution.<br /></pre></blockquote><prewrap="">You seem to be suggesting that we should make a variable-by-variable<br />decision aboutwhether SET variables roll back on ABORT or not. I think<br />that way madness lies; we could spend forever debatingwhich vars are<br />which, and then who will remember without consulting the documentation?<br /><br />I feel weshould just do it. Yeah, there might be some corner cases<br />where it's not the ideal behavior; but you haven't convincedme that<br />there are more cases where it's bad than where it's good. You sure<br />haven't convinced me that it'sworth making SET's behavior<br />nigh-unpredictable-without-a-manual, which is what per-variable behavior<br />wouldbe.<br /></pre></blockquote><pre wrap="">I am with Tom on this one. (Nice to see he is now arguing on my side.)<br/></pre></blockquote><pre wrap="">I vote against you. If a variable is local to the session, you<br />can changeit as you like without bothering any other user(session).<br />Automatic resetting of the varibales is rather confusingto me.<br /></pre></blockquote><pre wrap=""><br />I don't see how this relates to other users. All SET commandsthat can<br />be changed in psql are per backend, as far as I remember.</pre></blockquote> Per backend or per session?<br/><blockquote cite="mid200204080317.g383Hj511314@candle.pha.pa.us" type="cite"><pre wrap=""><br /><br /></pre></blockquote><br/><br />
On Fri, Apr 05, 2002 at 08:32:47PM -0500, Bruce Momjian wrote: > > Or, as I suggested above, extend the SELECT (and other querys?) syntax > > seems reasonable. More so than the non-standard 'use this index, really' > > types of extensions that other RDBMSs provide, that we've rightly avoided. > > I think we need timeout for all statement. The Oracle has: CREATE PROFILE statement with for example following options: CONNECT_TIME IDLE_TIME I think system resource control per user is more useful than simpleSET command. There is no problem add other limits likeQUERY_TIMEOUT. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
On Fri, Apr 05, 2002 at 02:13:26PM -0500, Tom Lane wrote:
> It seems clear to me that SET *should* roll back on abort. Just a
> matter of how important is it to fix.
I want control on this :-)
SET valname = 'vatdata' ON ROLLBACK UNSET; or SET valname = 'vatdata';
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
On Sun, Apr 07, 2002 at 01:01:07AM -0500, Peter Eisentraut wrote:
> Tom Lane writes:
>
> > I didn't say "transaction specific". I said that if you do a SET inside
> > a transaction block, and then the transaction is aborted, the effects of
> > the SET ought to roll back along with everything else you did inside
> > that transaction block. I'm not seeing what the argument is against
> > this.
>
> I consider SET variables metadata that are not affected by transactions.
> I should be able to change my mind about my session preferences in the
> middle of a transaction, no matter what happens to the data in it. Say
I agree with Peter. For example I have multi-encoding client program that changing client-encoding in the middle of
transactionand thischange not depend on transaction. And the other thing: I have DBdriver in an program there is not
possibledo SQL query outsitetransaction.
Is there some problem implement "SET ... ON ROLLBACK UNSET" ?
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > > > > Sorry I couldn't understand your point. > > > > > > It seems the simplest and the most certain way is to call > > > > > > 'SET QUERY_TIMEOUT per query. The way dosen't require > > > > > > RESET at all. Is the overhead an issue ? > > > > > > > > > > What about psql and libpq. Doing a timeout before every > query is a > > > > > pain. > > > > > > > > Psql and libpq would simply issue the query according to the > > > > user's request as they currently do. What's pain with it ? > > > > > > If they wanted to place a timeout on all queries in a session, they > > > would need a SET for every query, which seems like a pain. > > > > Oh I see. You mean users' pain ? > > Sorry I was unclear. > > > If a user wants to place a timeout on all the query, he > > would issue SET query_timeout command only once. > > I am confused. Above you state you want SET QUERY_TIMEOUT to be > per-query. I assume you mean that the timeout applies for only the next > query and is turned off after that. Hmm there seems a misunderstanding between you and I but I don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in your scenario ? In my scenario *SET QUERY_TIMEOUT* only registers the timeout value for subsequent queries. regards, Hiroshi inoue
Hiroshi Inoue wrote: > > I am confused. Above you state you want SET QUERY_TIMEOUT to be > > per-query. I assume you mean that the timeout applies for only the next > > query and is turned off after that. > > Hmm there seems a misunderstanding between you and I but I > don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in > your scenario ? In my scenario *SET QUERY_TIMEOUT* only > registers the timeout value for subsequent queries. SET QUERY_TIMEOUT does not start a timer. It makes sure each query after the SET is timed and automatically canceled if the single query exceeds the timeout interval. -- 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, Pennsylvania19026
Karel Zak wrote: > I agree with Peter. For example I have multi-encoding client program > that changing client-encoding in the middle of transaction and this > change not depend on transaction. And the other thing: I have DB > driver in an program there is not possible do SQL query outsite > transaction. No problem executing a SET inside its own transaction. The rollback happens only if the SET fails, which for a single SEt command, should be fine. > > Is there some problem implement "SET ... ON ROLLBACK UNSET" ? Seems kind of strange. If anything, I can imagine a NO ROLLBACK capability. However, because this can be easily done by executing the SET in its own transaction, it seems like overengineering. -- 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, Pennsylvania19026
Karel Zak <zakkr@zf.jcu.cz> writes:
> Is there some problem implement "SET ... ON ROLLBACK UNSET" ?
Yes. See my previous example concerning search_path: that variable
MUST be rolled back at transaction abort, else we risk its value being
invalid. We cannot offer the user a choice.
So far I have not seen one single example against SET rollback that
I thought was at all compelling. In all cases you can simply issue
the SET in a separate transaction if you want to be sure that its
effects persist. And there seems to be no consideration of the
possibility that applications might find SET rollback to be useful.
ISTM that the example with JDBC and query_timeout generalizes to other
parameters that you might want to set on a per-statement basis, such
as enable_seqscan or transform_null_equals. Consider
BEGIN;SET enable_seqscan = false;some-queries-that-might-fail;SET enable_seqscan = true;END;
This does not work as intended if the initial SET doesn't roll back
upon transaction failure. Yeah, you can restructure it to
SET enable_seqscan = false;BEGIN;some-queries-that-might-fail;END;SET enable_seqscan = true;
but what was that argument about some apps/drivers finding it
inconvenient to issue commands outside a transaction block?
regards, tom lane
Bruce Momjian wrote:
> > > > Sorry I couldn't understand your point.
> > > > It seems the simplest and the most certain way is to call
> > > > 'SET QUERY_TIMEOUT per query. The way dosen't require
> > > > RESET at all. Is the overhead an issue ?
> > >
> > > What about psql and libpq. Doing a timeout before every query is a
> > > pain.
> >
> > Psql and libpq would simply issue the query according to the
> > user's request as they currently do. What's pain with it ?
>
> If they wanted to place a timeout on all queries in a session, they
> would need a SET for every query, which seems like a pain.
Er, how many "applications" have you implemented by simply providing a schema and psql?
I mean, users normally don't use psql. And if you do, what's wrong with controlling the timeout yourself and
hitting^C when "you" time out? If you do it in a script, it's
yy... p p p p p.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Tom Lane wrote: > Karel Zak <zakkr@zf.jcu.cz> writes: > > Is there some problem implement "SET ... ON ROLLBACK UNSET" ? > > Yes. See my previous example concerning search_path: that variable > MUST be rolled back at transaction abort, else we risk its value being > invalid. We cannot offer the user a choice. Not really on topic, but I was wondering how you ensure that you correct the search path in case someone drops theschema? Is an invalid search path really that critical (read security issue)? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <janwieck@yahoo.com> writes:
> Is an invalid search path really that critical (read security
> issue)?
It's not a security issue (unless the OID counter wraps around soon
enough to let someone else get assigned the same OID for a namespace).
But it could be pretty annoying anyway, because the front element of
the search path is also the default creation target namespace. You
could create a bunch of tables and then be unable to access them later
for lack of a way to name them.
I'm not really excited about establishing positive interlocks across
backends to prevent DROPping a namespace that someone else has in their
search path ... but I do want to handle the simple local-effect cases,
like rollback of creation of a namespace.
regards, tom lane
Tom Lane wrote: > This does not work as intended if the initial SET doesn't roll back > upon transaction failure. Yeah, you can restructure it to > > SET enable_seqscan = false; > BEGIN; > some-queries-that-might-fail; > END; > SET enable_seqscan = true; > > but what was that argument about some apps/drivers finding it > inconvenient to issue commands outside a transaction block? Yes, and if you want to place the SET on a single statement in a multi-statement transaction, doing SET outside the transaction will not work either because it will apply to all statements in the transaction. -- 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, Pennsylvania19026
Jan Wieck wrote: > > > Psql and libpq would simply issue the query according to the > > > user's request as they currently do. What's pain with it ? > > > > If they wanted to place a timeout on all queries in a session, they > > would need a SET for every query, which seems like a pain. > > Er, how many "applications" have you implemented by simply > providing a schema and psql? Actually, I would assume nightly batch jobs are configured this way. > > I mean, users normally don't use psql. And if you do, what's > wrong with controlling the timeout yourself and hitting ^C > when "you" time out? If you do it in a script, it's Yes, clearly meaningless for interactive use. -- 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, Pennsylvania19026
> > I consider SET variables metadata that are not affected by transactions.
> Why? Again, the fact that historically they've not acted that way isn't
> sufficient reason for me.
Hmm. Historically, SET controls behaviors *out of band* with the normal
transaction mechanisms. There is strong precedent for this mechanism
*because it is a useful concept*, not simply because it has always been
done this way.
*If* some aspects of SET take on transactional behavior, then this
should be *in addition to* the current global scope for those commands.
What problem are we trying to solve with this? The topic came up in a
discussion on implementing timeouts for JDBC. afaik it has not come up
*in any context* for the last seven years, so maybe we should settle
down a bit and refocus on the problem at hand...
- Thomas
Thomas Lockhart wrote: > > > I consider SET variables metadata that are not affected by transactions. > > Why? Again, the fact that historically they've not acted that way isn't > > sufficient reason for me. > > Hmm. Historically, SET controls behaviors *out of band* with the normal > transaction mechanisms. There is strong precedent for this mechanism > *because it is a useful concept*, not simply because it has always been > done this way. OK, probably good time for summarization. First, consider this: BEGIN WORK;SET something;query fails;SET something else;COMMIT WORK; Under current behavior, the first SET is honored, while the second is ignored because the transaction is in ABORT state. I can see no logical reason for this behavior. We ignore normal queries during an ABORT because the transaction can't possibly change any data because it is aborted, and the previous non-SET statements in the transactions are rolled back. However, the SET commands are not. The jdbc timeout issue is this: BEGIN WORK;SET query_timeout=20;query fails;SET query_timeout=0;COMMIT WORK; In this case, with our current code, the first SET is done, but the second is ignored. To make this work, you would need this: BEGIN WORK;SET query_timeout=20;query fails;SET query_timeout=0;COMMIT WORK;SET query_timeout=0; which seems kind of strange. The last SET is needed because the query may abort and the second SET ignored. > *If* some aspects of SET take on transactional behavior, then this > should be *in addition to* the current global scope for those commands. My point is that SET already doesn't have session behavior because it is ignored if the transaction has already aborted. -- 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, Pennsylvania19026
Peter Eisentraut wrote: > Bruce Momjian writes: > > > OK, probably good time for summarization. First, consider this: > > > > BEGIN WORK; > > SET something; > > query fails; > > SET something else; > > COMMIT WORK; > > > > Under current behavior, the first SET is honored, while the second is > > ignored because the transaction is in ABORT state. I can see no logical > > reason for this behavior. > > But that is not a shortcoming of the SET command. The problem is that the > system does not accept any commands after one command has failed in a > transaction even though it could usefully do so. Uh, yes, we could allow the second SET to succeed even in an aborted transaction, but Tom says his schema stuff will not work in an aborted state, so Tom/I figured the only other option was rollback of the first SET. > > The jdbc timeout issue is this: > > > > > > BEGIN WORK; > > SET query_timeout=20; > > query fails; > > SET query_timeout=0; > > COMMIT WORK; > > > > In this case, with our current code, the first SET is done, but the > > second is ignored. > > Given appropriate functionality, you could rewrite this thus: > > BEGIN WORK; > SET FOR THIS TRANSACTION ONLY query_timeout=20; > query; > COMMIT WORK; Yes, but why bother with that when rollback of the first SET is cleaner and more predictable? -- 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, Pennsylvania19026
Tom Lane wrote: > Jan Wieck <janwieck@yahoo.com> writes: > > Is an invalid search path really that critical (read security > > issue)? > > It's not a security issue (unless the OID counter wraps around soon > enough to let someone else get assigned the same OID for a namespace). > But it could be pretty annoying anyway, because the front element of > the search path is also the default creation target namespace. You > could create a bunch of tables and then be unable to access them later > for lack of a way to name them. > > I'm not really excited about establishing positive interlocks across > backends to prevent DROPping a namespace that someone else has in their > search path ... but I do want to handle the simple local-effect cases, > like rollback of creation of a namespace. How are namespaces different from any other objects? Can I specify a foreign key reference to a table that was there at some time in the past? Can I create a view using functions that have been there last week? Sure, I can break those objects once created by dropping the underlying stuff, but that's another issue. If namespace dropping allows for creation of objects that cannot be dropped afterwards any more, I would callthat a bug or design flaw, which has to be fixed. Just preventing an invalid search path resulting from a rollbackoperation like in your example is totally insufficient. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Bruce Momjian writes: > OK, probably good time for summarization. First, consider this: > > BEGIN WORK; > SET something; > query fails; > SET something else; > COMMIT WORK; > > Under current behavior, the first SET is honored, while the second is > ignored because the transaction is in ABORT state. I can see no logical > reason for this behavior. But that is not a shortcoming of the SET command. The problem is that the system does not accept any commands after one command has failed in a transaction even though it could usefully do so. > The jdbc timeout issue is this: > > > BEGIN WORK; > SET query_timeout=20; > query fails; > SET query_timeout=0; > COMMIT WORK; > > In this case, with our current code, the first SET is done, but the > second is ignored. Given appropriate functionality, you could rewrite this thus: BEGIN WORK; SET FOR THIS TRANSACTION ONLY query_timeout=20; query; COMMIT WORK; -- Peter Eisentraut peter_e@gmx.net
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > Hiroshi Inoue wrote: > > > > > > > > I feel we should just do it. Yeah, there might be some corner cases > > > > where it's not the ideal behavior; but you haven't convinced me that > > > > there are more cases where it's bad than where it's good. You sure > > > > haven't convinced me that it's worth making SET's behavior > > > > nigh-unpredictable-without-a-manual, which is what > per-variable behavior > > > > would be. > > > > > > I am with Tom on this one. (Nice to see he is now arguing on > my side.) > > > > I vote against you. If a variable is local to the session, you > > can change it as you like without bothering any other user(session). > > Automatic resetting of the varibales is rather confusing to me. > > I don't see how this relates to other users. All SET commands that can > be changed in psql are per backend, as far as I remember. Sorry for my poor explanation. What I meant is that *Rollback* is to cancel the changes made to SQL-data or schemas not to put back the variables which are local to the session. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > I vote against you. If a variable is local to the session, you > > > can change it as you like without bothering any other user(session). > > > Automatic resetting of the varibales is rather confusing to me. > > > > I don't see how this relates to other users. All SET commands that can > > be changed in psql are per backend, as far as I remember. > > Sorry for my poor explanation. What I meant is that *Rollback* > is to cancel the changes made to SQL-data or schemas > not to put back the variables which are local to the session. OK, got it, so if someone makes a session change while in a transaction, and the transaction aborts, should the SET be rolled back too? If not, then we should honor the SET's that happen after the transaction aborts. However, Tom's schema changes require a db connection, so it is hard to honor the SET's once the transaction aborts. That is how we got to the abort all SET's in an aborted transaction. -- 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, Pennsylvania19026
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Sorry for my poor explanation. What I meant is that *Rollback*
> is to cancel the changes made to SQL-data or schemas
> not to put back the variables which are local to the session.
Uh, why? Seems to me you are asserting as a given exactly the
point that is under debate. Let me give a counterexample:
BEGIN;CREATE TEMP TABLE foo;something-erroneous;END;
The creation of the temp table will be rolled back on error, no?
Now the temp table is certainly session local --- ideally our
implementation would not let any other session see any trace of
it at all. (In practice it is visible if you know where to look,
but surely that's just an implementation artifact.)
If you argue that SETs should not roll back because they are
session-local, it seems to me that a logical consequence of that
position is that operations on temp tables should not roll back
either ... and that can hardly be deemed desirable.
regards, tom lane
Jan Wieck <janwieck@yahoo.com> writes:
> If namespace dropping allows for creation of objects that
> cannot be dropped afterwards any more, I would call that a
> bug or design flaw, which has to be fixed.
I will not require schema support to wait upon the existence of
dependency checking, if that's what you're suggesting.
This does suggest an interesting hole in our thoughts so far about
dependency checking. If someone is, say, trying to drop type T,
it's not really sufficient to verify that there are no existing
tables or functions referencing type T. What of created but as yet
uncommitted objects? Seems like a full defense would require being
able to obtain a lock on the object to be dropped, while creators
of references must obtain some conflicting lock that they hold until
they commit. Right now we only have locks on tables ... seems like
that's not sufficient.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> But that is not a shortcoming of the SET command. The problem is that the
>> system does not accept any commands after one command has failed in a
>> transaction even though it could usefully do so.
In a situation where the reason for failure was a syntax error, it seems
to me quite dangerous to try to execute any further commands; you may
not be executing what the user thought he typed. So I'm leery of any
proposals that we allow SETs to execute in transaction-abort state,
even if the implementation could support it.
> Uh, yes, we could allow the second SET to succeed even in an aborted
> transaction, but Tom says his schema stuff will not work in an aborted
> state, so Tom/I figured the only other option was rollback of the first
> SET.
The search_path case is the main reason why I'm intent on changing
the behavior of SET; without that, I'd just leave well enough alone.
Possibly some will suggest that search_path shouldn't be a SET variable
because it needs to be able to be rolled back on error. But what else
should it be? It's definitely per-session status, not persistent
database state. I don't much care for the notion of having SET act
differently for some variables than others, or requiring people to use
a different command for some variables than others.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Sorry for my poor explanation. What I meant is that *Rollback* > > is to >> cancel the changes made to SQL-data or schemas This line is a quote from SQL99 not my creation. > > not to put back the variables which are local to the session. > > Uh, why? Seems to me you are asserting as a given exactly the > point that is under debate. Let me give a counterexample: > > BEGIN; > CREATE TEMP TABLE foo; > something-erroneous; > END; > > The creation of the temp table will be rolled back on error, no? ??? TEMP TABLE is a SQL-data not a variable. I don't think rolling back SETs makes things plain. regards, Hiroshi Inoue
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
>
> Hiroshi Inoue wrote:
> > > I am confused. Above you state you want SET QUERY_TIMEOUT to be
> > > per-query. I assume you mean that the timeout applies for
> only the next
> > > query and is turned off after that.
> >
> > Hmm there seems a misunderstanding between you and I but I
> > don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in
> > your scenario ? In my scenario *SET QUERY_TIMEOUT* only
> > registers the timeout value for subsequent queries.
>
> SET QUERY_TIMEOUT does not start a timer. It makes sure each query
> after the SET is timed and automatically canceled if the single query
> exceeds the timeout interval.
OK using your example, one by one
BEGIN WORK; SET query_timeout=20; query fails; SET query_timeout=0;
For what the SET was issued ?
What command is issued if the query was successful ?
COMMIT WORK;
regards,
Hiroshi Inoue
Hiroshi Inoue wrote: > > -----Original Message----- > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > Hiroshi Inoue wrote: > > > > I am confused. Above you state you want SET QUERY_TIMEOUT to be > > > > per-query. I assume you mean that the timeout applies for > > only the next > > > > query and is turned off after that. > > > > > > Hmm there seems a misunderstanding between you and I but I > > > don't see what it is. Does *SET QUERY_TIMEOUT* start a timer in > > > your scenario ? In my scenario *SET QUERY_TIMEOUT* only > > > registers the timeout value for subsequent queries. > > > > SET QUERY_TIMEOUT does not start a timer. It makes sure each query > > after the SET is timed and automatically canceled if the single query > > exceeds the timeout interval. > > OK using your example, one by one > > BEGIN WORK; > SET query_timeout=20; > query fails; > SET query_timeout=0; > > For what the SET was issued ? > What command is issued if the query was successful ? > > COMMIT WORK; Here, SET should only to the query labeled "query fails". However, right now, because the query failed, the second SET would not be seen, and the timout would apply to all remaining queries in the session. -- 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, Pennsylvania19026
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > OK using your example, one by one > > > > BEGIN WORK; > > SET query_timeout=20; > > query fails; > > SET query_timeout=0; > > > > For what the SET was issued ? > > What command is issued if the query was successful ? > > > > COMMIT WORK; > > Here, SET should only to the query labeled "query fails". Why should the SET query_timeout = 0 command be issued only when the query failed ? Is it a JDBC driver's requirement or some applications' requirements which uses the JDBC driver ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > -----Original Message----- > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > > > OK using your example, one by one > > > > > > BEGIN WORK; > > > SET query_timeout=20; > > > query fails; > > > SET query_timeout=0; > > > > > > For what the SET was issued ? > > > What command is issued if the query was successful ? > > > > > > COMMIT WORK; > > > > Here, SET should only to the query labeled "query fails". > > Why should the SET query_timeout = 0 command be issued > only when the query failed ? Is it a JDBC driver's requirement > or some applications' requirements which uses the JDBC driver ? They want the timeout for only the one statement, so they have to set it to non-zero before the statement, and to zero after the statement. In our current code, if the query fails, the setting to zero is ignored, meaning all following queries have the timeout, even ones outside that transaction. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > -----Original Message----- > > > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > > > > > > > > OK using your example, one by one > > > > > > > > BEGIN WORK; > > > > SET query_timeout=20; > > > > query fails; > > > > SET query_timeout=0; > > > > > > > > For what the SET was issued ? > > > > What command is issued if the query was successful ? > > > > > > > > COMMIT WORK; > > > > > > Here, SET should only to the query labeled "query fails". > > > > Why should the SET query_timeout = 0 command be issued > > only when the query failed ? Is it a JDBC driver's requirement > > or some applications' requirements which uses the JDBC driver ? > > They want the timeout for only the one statement, so they have to set it > to non-zero before the statement, and to zero after the statement. Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT command immediately in the scenario ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > Why should the SET query_timeout = 0 command be issued > > > only when the query failed ? Is it a JDBC driver's requirement > > > or some applications' requirements which uses the JDBC driver ? > > > > They want the timeout for only the one statement, so they have to set it > > to non-zero before the statement, and to zero after the statement. > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT > command immediately in the scenario ? Yes. If we don't make the SET rollback-able, we have to do all sorts of tricks in jdbc so aborted transactions get the proper SET value. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > > Why should the SET query_timeout = 0 command be issued > > > > only when the query failed ? Is it a JDBC driver's requirement > > > > or some applications' requirements which uses the JDBC driver ? > > > > > > They want the timeout for only the one statement, so they have to set it > > > to non-zero before the statement, and to zero after the statement. > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT > > command immediately in the scenario ? > > Yes. If we don't make the SET rollback-able, we have to do all sorts of > tricks in jdbc so aborted transactions get the proper SET value. In my scenario, setQueryTimeout() only saves the timeout value and issues the corrsponding SET QUERY_TIMEOUT command immediately before each query if necessary. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Hiroshi Inoue wrote: > > > > > Why should the SET query_timeout = 0 command be issued > > > > > only when the query failed ? Is it a JDBC driver's requirement > > > > > or some applications' requirements which uses the JDBC driver ? > > > > > > > > They want the timeout for only the one statement, so they have to set it > > > > to non-zero before the statement, and to zero after the statement. > > > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT > > > command immediately in the scenario ? > > > > Yes. If we don't make the SET rollback-able, we have to do all sorts of > > tricks in jdbc so aborted transactions get the proper SET value. > > In my scenario, setQueryTimeout() only saves the timeout > value and issues the corrsponding SET QUERY_TIMEOUT command > immediately before each query if necessary. Yes, we can do that, but it requires an interface like odbc or jdbc. It is hard to use for libpq or psql. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > They want the timeout for only the one statement, so they have to set it > > > > > to non-zero before the statement, and to zero after the statement. > > > > > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT > > > > command immediately in the scenario ? > > > > > > Yes. If we don't make the SET rollback-able, we have to do all sorts of > > > tricks in jdbc so aborted transactions get the proper SET value. > > > > In my scenario, setQueryTimeout() only saves the timeout > > value and issues the corrsponding SET QUERY_TIMEOUT command > > immediately before each query if necessary. > > Yes, we can do that, Something like my scenario is needed because there could be more than 1 statement objects with relatively different query timeout at the same time in theory. > but it requires an interface like odbc or jdbc. It > is hard to use for libpq or psql. We shouldn't expect too much on psql in the first place because it isn't procedural. I don't expect too much on libpq either because it's a low level interface. However applications which use libpq could do like odbc or jdbc does. Or libpq could also provide a function which encap- sulates the query timeout handling if necessary. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Hiroshi Inoue wrote: > > > Bruce Momjian wrote: > > > > > > > They want the timeout for only the one statement, so they have to set it > > > > > > to non-zero before the statement, and to zero after the statement. > > > > > > > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT > > > > > command immediately in the scenario ? > > > > > > > > Yes. If we don't make the SET rollback-able, we have to do all sorts of > > > > tricks in jdbc so aborted transactions get the proper SET value. > > > > > > In my scenario, setQueryTimeout() only saves the timeout > > > value and issues the corrsponding SET QUERY_TIMEOUT command > > > immediately before each query if necessary. > > > > Yes, we can do that, > > Something like my scenario is needed because there could be > more than 1 statement objects with relatively different > query timeout at the same time in theory. Yes, if you want multiple timeouts, you clearly could go in that direction. Right now, we are considering only single-statement timing and no one has asked for multiple timers. > > > but it requires an interface like odbc or jdbc. It > > is hard to use for libpq or psql. > > We shouldn't expect too much on psql in the first place > because it isn't procedural. I don't expect too much on > libpq either because it's a low level interface. However > applications which use libpq could do like odbc or jdbc > does. Or libpq could also provide a function which encap- > sulates the query timeout handling if necessary. I certainly would like _something_ that works in psql/libpq, and the simple SET QUERY_TIMEOUT does work for them. More sophisticated stuff probably should be done in the application or interface. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > Hiroshi Inoue wrote: > > > > Bruce Momjian wrote: > > > > > > > > > They want the timeout for only the one statement, so they have to set it > > > > > > > to non-zero before the statement, and to zero after the statement. > > > > > > > > > > > > Does setQueryTimeout() issue a corresponding SET QUERY_TIMEOUT > > > > > > command immediately in the scenario ? > > > > > > > > > > Yes. If we don't make the SET rollback-able, we have to do all sorts of > > > > > tricks in jdbc so aborted transactions get the proper SET value. > > > > > > > > In my scenario, setQueryTimeout() only saves the timeout > > > > value and issues the corrsponding SET QUERY_TIMEOUT command > > > > immediately before each query if necessary. > > > > > > Yes, we can do that, > > > > Something like my scenario is needed because there could be > > more than 1 statement objects with relatively different > > query timeout at the same time in theory. > > Yes, if you want multiple timeouts, you clearly could go in that > direction. Right now, we are considering only single-statement timing > and no one has asked for multiple timers. I don't ask multiple timers. ODBC driver would be able to handle multiple timeouts without multiple timers in my scenario. > > > but it requires an interface like odbc or jdbc. It > > > is hard to use for libpq or psql. > > > > We shouldn't expect too much on psql in the first place > > because it isn't procedural. I don't expect too much on > > libpq either because it's a low level interface. However > > applications which use libpq could do like odbc or jdbc > > does. Or libpq could also provide a function which encap- > > sulates the query timeout handling if necessary. > > I certainly would like _something_ that works in psql/libpq, Please don't make things complicated by sticking to such low level interfaces. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > Yes, if you want multiple timeouts, you clearly could go in that > > direction. Right now, we are considering only single-statement timing > > and no one has asked for multiple timers. > > I don't ask multiple timers. ODBC driver would be able > to handle multiple timeouts without multiple timers in > my scenario. I understand. > > > > but it requires an interface like odbc or jdbc. It > > > > is hard to use for libpq or psql. > > > > > > We shouldn't expect too much on psql in the first place > > > because it isn't procedural. I don't expect too much on > > > libpq either because it's a low level interface. However > > > applications which use libpq could do like odbc or jdbc > > > does. Or libpq could also provide a function which encap- > > > sulates the query timeout handling if necessary. > > > > I certainly would like _something_ that works in psql/libpq, > > Please don't make things complicated by sticking to such > low level interfaces. OK, what is your proposal? -- 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, Pennsylvania19026
On Mon, Apr 08, 2002 at 12:28:18PM -0400, Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > OK, probably good time for summarization. First, consider this:
> >
> > BEGIN WORK;
> > SET something;
> > query fails;
> > SET something else;
> > COMMIT WORK;
> >
> > Under current behavior, the first SET is honored, while the second is
> > ignored because the transaction is in ABORT state. I can see no logical
> > reason for this behavior.
>
> But that is not a shortcoming of the SET command. The problem is that the
> system does not accept any commands after one command has failed in a
> transaction even though it could usefully do so.
>
> > The jdbc timeout issue is this:
> >
> >
> > BEGIN WORK;
> > SET query_timeout=20;
> > query fails;
> > SET query_timeout=0;
> > COMMIT WORK;
> >
> > In this case, with our current code, the first SET is done, but the
> > second is ignored.
>
> Given appropriate functionality, you could rewrite this thus:
>
> BEGIN WORK;
> SET FOR THIS TRANSACTION ONLY query_timeout=20;
> query;
> COMMIT WORK;
If I compare Peter's and Bruce's examples the Peter is still winner :-)
Sorry, but a code with "set-it-after-abort" seems ugly.
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
On Mon, Apr 08, 2002 at 01:03:41PM -0400, Tom Lane wrote: > The search_path case is the main reason why I'm intent on changing > the behavior of SET; without that, I'd just leave well enough alone. Is there more variables like "search_path"? If not, I unsure if oneitem is good consideration for change others things. > Possibly some will suggest that search_path shouldn't be a SET variable > because it needs to be able to be rolled back on error. But what else > should it be? It's definitely per-session status, not persistent It's good point. Why not make it more transparent? You wantencapsulate it to standard and current SET statement, but if it'ssomethingdifferent why not use for it different statement? SET SESSION search_path TO 'something'; (...or something other) Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Heh pardon me but... I was under the impression that for a transaction either all commands succeed or all commands fail, atleast according to everything I've ever read. So followign that all SETs done within the scope of a BEGIN/COMMIT pair should only take effect if the whole set finishes, if not the system shoudl roll back to the way it was before the BEGIN. I might be missing something though, I just got onto the list and there might be other parts of the thread I missed.... Karel Zak wrote: >On Mon, Apr 08, 2002 at 01:03:41PM -0400, Tom Lane wrote: > >>The search_path case is the main reason why I'm intent on changing >>the behavior of SET; without that, I'd just leave well enough alone. >> > > Is there more variables like "search_path"? If not, I unsure if one > item is good consideration for change others things. > >>Possibly some will suggest that search_path shouldn't be a SET variable >>because it needs to be able to be rolled back on error. But what else >>should it be? It's definitely per-session status, not persistent >> > > It's good point. Why not make it more transparent? You want > encapsulate it to standard and current SET statement, but if it's > something different why not use for it different statement? > > SET SESSION search_path TO 'something'; > > (...or something other) > > Karel >
Karel Zak <zakkr@zf.jcu.cz> writes:
> It's good point. Why not make it more transparent? You want
> encapsulate it to standard and current SET statement, but if it's
> something different why not use for it different statement?
> SET SESSION search_path TO 'something';
But a plain SET is also setting the value for the session. What's
the difference? Why should a user remember that he must use this
syntax for search_path, and not for any other variables (or perhaps
only one or two other ones, further down the road)?
regards, tom lane
Michael Loftis writes:
> I was under the impression that for a transaction either all commands
> succeed or all commands fail, atleast according to everything I've ever
> read.
That's an urban legend.
A transaction guarantees (among other things) that all modifications to
the database with the transaction are done atomicly (either all or done or
none). This does not extend to the commands that supposedly initiate such
modifications.
Take out a database other than PostgreSQL and do
BEGIN; -- or whatever they use; might be implicit
INSERT INTO existing_table ('legal value');
barf;
COMMIT;
The INSERT will most likely succeed. The reason is that "barf" does not
modify or access the data in the database, so it does not affect the
transactional integrity of the database.
We are trying to make the same argument for SET. SET does not modify the
database, so it doesn't have to fall under transaction control.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
> Michael Loftis writes:
>
> > I was under the impression that for a transaction either all commands
> > succeed or all commands fail, atleast according to everything I've ever
> > read.
>
> That's an urban legend.
>
> A transaction guarantees (among other things) that all modifications to
> the database with the transaction are done atomicly (either all or done or
> none). This does not extend to the commands that supposedly initiate such
> modifications.
>
> Take out a database other than PostgreSQL and do
>
> BEGIN; -- or whatever they use; might be implicit
> INSERT INTO existing_table ('legal value');
> barf;
> COMMIT;
>
> The INSERT will most likely succeed. The reason is that "barf" does not
> modify or access the data in the database, so it does not affect the
> transactional integrity of the database.
Ewe, we do fail that test.
> We are trying to make the same argument for SET. SET does not modify the
> database, so it doesn't have to fall under transaction control.
OK, we have three possibilities:
o All SETs are honored in an aborted transactiono No SETs are honored in an aborted transactiono Some SETs are
honoredin an aborted transaction (current)
I think the problem is our current behavior. I don't think anyone can
say our it is correct (only honor SET before the transaction reaches
abort state). Whether we want the first or second is the issue, I think.
-- 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,
Pennsylvania19026
Bruce Momjian wrote: > > OK, we have three possibilities: > > o All SETs are honored in an aborted transaction > o No SETs are honored in an aborted transaction > o Some SETs are honored in an aborted transaction (current) > > I think the problem is our current behavior. I don't think anyone can > say our it is correct (only honor SET before the transaction reaches > abort state). Whether we want the first or second is the issue, I think. I think the current state is not that bad at least is better than the first. I don't think it's a *should be* kind of thing and we shouldn't stick to it any longer. regards, Hiroshi Inoue
Tom Lane wrote: > > Karel Zak <zakkr@zf.jcu.cz> writes: > > It's good point. Why not make it more transparent? You want > > encapsulate it to standard and current SET statement, but if it's > > something different why not use for it different statement? > > > SET SESSION search_path TO 'something'; > > But a plain SET is also setting the value for the session. What's > the difference? Why should a user remember that he must use this > syntax for search_path, and not for any other variables (or perhaps > only one or two other ones, further down the road)? ISTM what Karel meant is that if the search_path is a much more significant variable than others you had better express the difference using a different statement. I agree with Karel though I don't know how siginificant the varible is. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > OK, we have three possibilities: > > > > o All SETs are honored in an aborted transaction > > o No SETs are honored in an aborted transaction > > o Some SETs are honored in an aborted transaction (current) > > > > I think the problem is our current behavior. I don't think anyone can > > say our it is correct (only honor SET before the transaction reaches > > abort state). Whether we want the first or second is the issue, I think. > > I think the current state is not that bad at least > is better than the first. Oops does the first mean rolling back the variables on abort ? If so I made a mistake. The current is better than the second. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Hiroshi Inoue wrote: > > > > Bruce Momjian wrote: > > > > > > OK, we have three possibilities: > > > > > > o All SETs are honored in an aborted transaction > > > o No SETs are honored in an aborted transaction > > > o Some SETs are honored in an aborted transaction (current) > > > > > > I think the problem is our current behavior. I don't think anyone can > > > say our it is correct (only honor SET before the transaction reaches > > > abort state). Whether we want the first or second is the issue, I think. > > > > I think the current state is not that bad at least > > is better than the first. > > Oops does the first mean rolling back the variables on abort ? > If so I made a mistake. The current is better than the second. The second means all SET's are rolled back on abort. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Hiroshi Inoue wrote: > > > > > > Bruce Momjian wrote: > > > > > > > > OK, we have three possibilities: > > > > > > > > o All SETs are honored in an aborted transaction > > > > o No SETs are honored in an aborted transaction > > > > o Some SETs are honored in an aborted transaction (current) > > > > > > > > I think the problem is our current behavior. I don't think anyone can > > > > say our it is correct (only honor SET before the transaction reaches > > > > abort state). Whether we want the first or second is the issue, I think. > > > > > > I think the current state is not that bad at least > > > is better than the first. > > > > Oops does the first mean rolling back the variables on abort ? > > If so I made a mistake. The current is better than the second. > > The second means all SET's are rolled back on abort. I see. BTW what varibles are rolled back on abort currently ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > Oops does the first mean rolling back the variables on abort ? > > > If so I made a mistake. The current is better than the second. > > > > The second means all SET's are rolled back on abort. > > I see. > BTW what varibles are rolled back on abort currently ? Currently, none, though the SET commands after the query aborts are ignored, which is effectively the same as rolling them back. BEGIN WORK;SET x=3;failed query;SET x=5;COMMIT; In this case, x=3 at end of query. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > > Oops does the first mean rolling back the variables on abort ? > > > > If so I made a mistake. The current is better than the second. > > > > > > The second means all SET's are rolled back on abort. > > > > I see. > > BTW what varibles are rolled back on abort currently ? > > Currently, none, ??? What do you mean by o Some SETs are honored in an aborted transaction (current) ? Is the current state different from o All SETs are honored in an aborted transaction ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Hiroshi Inoue wrote: > > > > > Oops does the first mean rolling back the variables on abort ? > > > > > If so I made a mistake. The current is better than the second. > > > > > > > > The second means all SET's are rolled back on abort. > > > > > > I see. > > > BTW what varibles are rolled back on abort currently ? > > > > Currently, none, > > ??? What do you mean by > o Some SETs are honored in an aborted transaction (current) > ? > Is the current state different from > o All SETs are honored in an aborted transaction > ? In the case of: BEGIN WORK;SET x=1;bad query that aborts transaction;SET x=2;COMMIT WORK; Only the first SET is done, so at the end, x = 1. If all SET's were honored, x = 2. If no SETs in an aborted transaction were honored, x would equal whatever it was before the BEGIN WORK above. -- 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, Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > Hiroshi Inoue wrote: > > > > > > Oops does the first mean rolling back the variables on abort ? > > > > > > If so I made a mistake. The current is better than the second. > > > > > > > > > > The second means all SET's are rolled back on abort. > > > > > > > > I see. > > > > BTW what varibles are rolled back on abort currently ? > > > > > > Currently, none, > > > > ??? What do you mean by > > o Some SETs are honored in an aborted transaction (current) > > ? > > Is the current state different from > > o All SETs are honored in an aborted transaction > > ? > > In the case of: > > BEGIN WORK; > SET x=1; > bad query that aborts transaction; > SET x=2; > COMMIT WORK; > > Only the first SET is done, so at the end, x = 1. If all SET's were > honored, x = 2. If no SETs in an aborted transaction were honored, x > would equal whatever it was before the BEGIN WORK above. IMHO o No SETs are honored in an aborted transaction(current) The first SET isn't done in an aborted transaction. regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > > ??? What do you mean by > > > o Some SETs are honored in an aborted transaction (current) > > > ? > > > Is the current state different from > > > o All SETs are honored in an aborted transaction > > > ? > > > > In the case of: > > > > BEGIN WORK; > > SET x=1; > > bad query that aborts transaction; > > SET x=2; > > COMMIT WORK; > > > > Only the first SET is done, so at the end, x = 1. If all SET's were > > honored, x = 2. If no SETs in an aborted transaction were honored, x > > would equal whatever it was before the BEGIN WORK above. > > IMHO > o No SETs are honored in an aborted transaction(current) > > The first SET isn't done in an aborted transaction. Well, yes, when I say aborted transaction, I mean the entire transaction, not just the part after the abort happens. All non-SET commands in the transaction are rolled back already. I can't think of a good argument for our current behavior. -- 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, Pennsylvania19026
Hiroshi Inoue wrote: > > > ??? What do you mean by > > > o Some SETs are honored in an aborted transaction (current) > > > ? > > > Is the current state different from > > > o All SETs are honored in an aborted transaction > > > ? > > > > In the case of: > > > > BEGIN WORK; > > SET x=1; > > bad query that aborts transaction; > > SET x=2; > > COMMIT WORK; > > > > Only the first SET is done, so at the end, x = 1. If all SET's were > > honored, x = 2. If no SETs in an aborted transaction were honored, x > > would equal whatever it was before the BEGIN WORK above. > > IMHO > o No SETs are honored in an aborted transaction(current) > > The first SET isn't done in an aborted transaction. I guess my point is that with our current code, there is a distinction that SETs are executed before a transaction aborts, but are ignored after a transaction aborts, even if the SETs are in the same transaction. -- 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, Pennsylvania19026
Peter Eisentraut <peter_e@gmx.net> writes:
> Take out a database other than PostgreSQL and do
> BEGIN; -- or whatever they use; might be implicit
> INSERT INTO existing_table ('legal value');
> barf;
> COMMIT;
> The INSERT will most likely succeed. The reason is that "barf" does not
> modify or access the data in the database, so it does not affect the
> transactional integrity of the database.
No; this example is completely irrelevant to our discussion. The reason
that (some) other DBMSes will allow the INSERT to take effect in the
above case is that they have savepoints, and the failure of the "barf"
command only rolls back to the savepoint not to the start of the
transaction. It's a generally-acknowledged shortcoming that we don't
have savepoints ... but this has no relevance to the question of whether
SETs should be rolled back or not. If we did have savepoints then I'd
be saying that SETs should roll back to a savepoint just like everything
else.
Please note that even in those other databases, if one replaces the
COMMIT with ROLLBACK in the above scenario, the effects of the INSERT
*will* roll back. Transpose this into current Postgres, and replace
INSERT with SET, and the effects do *not* roll back. How is that a
good idea?
regards, tom lane
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Take out a database other than PostgreSQL and do
>
> > BEGIN; -- or whatever they use; might be implicit
> > INSERT INTO existing_table ('legal value');
> > barf;
> > COMMIT;
>
> > The INSERT will most likely succeed. The reason is that "barf" does not
> > modify or access the data in the database, so it does not affect the
> > transactional integrity of the database.
>
> No; this example is completely irrelevant to our discussion. The reason
Actually, we could probably prevent transaction abort on syntax(yacc)
errors, but the other errors like mistyped table names would be hard to
prevent a rollback, so I guess we just roll back on any error.
-- 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,
Pennsylvania19026
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > > ??? What do you mean by > > > > o Some SETs are honored in an aborted transaction (current) > > > > ? > > > > Is the current state different from > > > > o All SETs are honored in an aborted transaction > > > > ? > > > > > > In the case of: > > > > > > BEGIN WORK; > > > SET x=1; > > > bad query that aborts transaction; > > > SET x=2; > > > COMMIT WORK; > > > > > > Only the first SET is done, so at the end, x = 1. If all SET's were > > > honored, x = 2. If no SETs in an aborted transaction were honored, x > > > would equal whatever it was before the BEGIN WORK above. > > > > IMHO > > o No SETs are honored in an aborted transaction(current) > > > > The first SET isn't done in an aborted transaction. > > I guess my point is that with our current code, there is a distinction > that SETs are executed before a transaction aborts, but are ignored > after a transaction aborts, even if the SETs are in the same > transaction. Not only SET commands but also most commands are ignored after a transaction aborts currently. SET commands are out of transactional control but it doesn't mean they are never ignore(rejecte)d. regards, Hiroshi Inoue
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In the case of:
> BEGIN WORK;
> SET x=1;
> bad query that aborts transaction;
> SET x=2;
> COMMIT WORK;
> Only the first SET is done, so at the end, x = 1.
Perhaps even more to the point:
SET x=0;BEGIN;SET x=1;bad query;SET x=2;ROLLBACK;
Now x=1. How is this sensible?
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Actually, we could probably prevent transaction abort on syntax(yacc)
> errors, but the other errors like mistyped table names would be hard to
> prevent a rollback, so I guess we just roll back on any error.
I don't think that what we categorize as an error or not is very
relevant to the discussion, either. The real point is: should SET
have rollback behavior similar to other SQL commands, or not?
If we had savepoints, or ignorable syntax errors, or other frammishes
this question would still be the same.
regards, tom lane
...
> Please note that even in those other databases, if one replaces the
> COMMIT with ROLLBACK in the above scenario, the effects of the INSERT
> *will* roll back. Transpose this into current Postgres, and replace
> INSERT with SET, and the effects do *not* roll back. How is that a
> good idea?
Well, as you should have concluded by now, "good" is not the same for
everyone ;)
Frankly, I've been happy with the current SET behavior, but would also
be willing to consider most of the alternatives which have been
suggested, including ones you have dismissed out of hand. Constraints
which seem to have been imposed include:
1) All commands starting with "SET" must have the same transactional
semantics. I'll agree that it might be nice for consistancy, but imho is
not absolutely required.
2) No commands which could be expected to start with "SET" will start
with some other keyword. If we do have "set class" commands which have
different transactional semantics, then we could explore alternative
syntax for specifying each category.
3) "SET" commands must respect transactions. I'm happy with the idea
that these commands are out of band and take effect immediately. And if
they take effect even in the middle of a failing/failed transaction,
that is OK too. The surrounding code would have reset the values anyway,
if necessary.
I do have a concern about how to implement some of the SET commands if
we *do* respect transactional semantics. For example, SET TIME ZONE
saves the current value of an environment variable (if available), and
would need *at least* a "before transaction" and "after transaction
started" pair of values. How would we propagate SET variables to
transaction-specific structures, clearing or resetting them later? Right
now these variables are pretty independent and can be accessed through
global storage; having transactional semantics means that the
interdependencies between different variable types in the SET handlers
may increase.
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
> I do have a concern about how to implement some of the SET commands if
> we *do* respect transactional semantics. For example, SET TIME ZONE
> saves the current value of an environment variable (if available), and
> would need *at least* a "before transaction" and "after transaction
> started" pair of values.
I intended for guc.c to manage this bookkeeping, thus freeing individual
modules from worrying about it. That would require us to transpose the
last few special-cased SET variables into generic GUC variables, but
I consider that a Good Thing anyway.
regards, tom lane
Thomas Lockhart writes: > 1) All commands starting with "SET" must have the same transactional > semantics. I'll agree that it might be nice for consistancy, but imho is > not absolutely required. This rule is already violated anyway. SET TRANSACTION ISOLATION, SET CONSTRAINTS, SET SESSION AUTHORIZATION, and SET mostly_anything_else already behave quite differently. -- Peter Eisentraut peter_e@gmx.net
I have added this to the TODO list, with a question mark. Hope this is
OK with everyone.
o Abort SET changes made in aborted transactions (?)
---------------------------------------------------------------------------
Tom Lane wrote:
> Thomas Lockhart <lockhart@fourpalms.org> writes:
> > I do have a concern about how to implement some of the SET commands if
> > we *do* respect transactional semantics. For example, SET TIME ZONE
> > saves the current value of an environment variable (if available), and
> > would need *at least* a "before transaction" and "after transaction
> > started" pair of values.
>
> I intended for guc.c to manage this bookkeeping, thus freeing individual
> modules from worrying about it. That would require us to transpose the
> last few special-cased SET variables into generic GUC variables, but
> I consider that a Good Thing anyway.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-- 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,
Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have added this to the TODO list, with a question mark. Hope this is
> OK with everyone.
> o Abort SET changes made in aborted transactions (?)
Actually, I was planning to make only search_path act that way, because
of all the push-back I'd gotten on applying it to other SET variables.
search_path really *has* to have it, but if there's anyone who agrees
with me about doing it for all SET vars, they didn't speak up :-(
regards, tom lane
Tom Lane wrote: >Bruce Momjian <pgman@candle.pha.pa.us> writes: > >>I have added this to the TODO list, with a question mark. Hope this is >>OK with everyone. >> > >> o Abort SET changes made in aborted transactions (?) >> > >Actually, I was planning to make only search_path act that way, because >of all the push-back I'd gotten on applying it to other SET variables. >search_path really *has* to have it, but if there's anyone who agrees >with me about doing it for all SET vars, they didn't speak up :-( > I did and do, strongly. TRANSACTIONS are supposed to leave things as they were before the BEGIN. It either all happens or it all doesnt' happen. If you need soemthing inside of a transaction to go irregardless then it shouldn't be within the transaction. >regards, tom lane >
Michael Loftis wrote: > > Tom Lane wrote: > > >Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > >>I have added this to the TODO list, with a question mark. Hope this is > >>OK with everyone. > >> > > > >> o Abort SET changes made in aborted transactions (?) > >> > > > >Actually, I was planning to make only search_path act that way, because > >of all the push-back I'd gotten on applying it to other SET variables. > >search_path really *has* to have it, but if there's anyone who agrees > >with me about doing it for all SET vars, they didn't speak up :-( > > > I did and do, strongly. TRANSACTIONS are supposed to leave things as > they were before the BEGIN. It either all happens or it all doesnt' > happen. If you need soemthing inside of a transaction to go > irregardless then it shouldn't be within the transaction. Oops is this issue still living ? I object to the TODO(why ????) strongly. Please remove it from the TODO first and put it back to the neutral position. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: > Michael Loftis wrote: > > > > Tom Lane wrote: > > > > >Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > > > >>I have added this to the TODO list, with a question mark. Hope this is > > >>OK with everyone. > > >> > > > > > >> o Abort SET changes made in aborted transactions (?) > > >> > > > > > >Actually, I was planning to make only search_path act that way, because > > >of all the push-back I'd gotten on applying it to other SET variables. > > >search_path really *has* to have it, but if there's anyone who agrees > > >with me about doing it for all SET vars, they didn't speak up :-( > > > > > I did and do, strongly. TRANSACTIONS are supposed to leave things as > > they were before the BEGIN. It either all happens or it all doesnt' > > happen. If you need soemthing inside of a transaction to go > > irregardless then it shouldn't be within the transaction. > > Oops is this issue still living ? > I object to the TODO(why ????) strongly. > Please remove it from the TODO first and put it back > to the neutral position. OK, how is this: o Abort all or commit all SET changes made in an aborted transaction Is this neutral? I don't think our current behavior is defended by anyone. -- 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, Pennsylvania19026
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I have added this to the TODO list, with a question mark. Hope this is > > OK with everyone. > > > o Abort SET changes made in aborted transactions (?) > > Actually, I was planning to make only search_path act that way, because > of all the push-back I'd gotten on applying it to other SET variables. > search_path really *has* to have it, but if there's anyone who agrees > with me about doing it for all SET vars, they didn't speak up :-( Woh, this all started because of timeout, which needs this fix too. We certainly need something and I don't want to get into on of those "we can't all decide, so we do nothing" situations. I have updated the TODO to: o Abort all or commit all SET changes made in an aborted transaction I don't think our current behavior is defended by anyone. Is abort all or commit all the only two choices? If so, we will take a vote and be done with it. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I have updated the TODO to:
> o Abort all or commit all SET changes made in an aborted transaction
> I don't think our current behavior is defended by anyone.
Hiroshi seems to like it ...
However, "commit SETs even after an error" is most certainly NOT
acceptable. It's not even sensible --- what if the SETs themselves
throw errors, or are depending on the results of failed non-SET
commands; will you try to commit them anyway?
It seems to me that the choices we realistically have are
(a) leave the behavior the way it is
(b) cause all SETs in an aborted transaction to roll back.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I have updated the TODO to: > > o Abort all or commit all SET changes made in an aborted transaction > > I don't think our current behavior is defended by anyone. > > Hiroshi seems to like it ... > > However, "commit SETs even after an error" is most certainly NOT > acceptable. It's not even sensible --- what if the SETs themselves > throw errors, or are depending on the results of failed non-SET > commands; will you try to commit them anyway? > > It seems to me that the choices we realistically have are > > (a) leave the behavior the way it is > > (b) cause all SETs in an aborted transaction to roll back. I disagree. You commit all the SET's you can, even if in aborted transactions. If they throw an error, or rely on a previous non-SET that aborted, oh well. That is what some are asking for. -- 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, Pennsylvania19026
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I have updated the TODO to: > > o Abort all or commit all SET changes made in an aborted transaction > > I don't think our current behavior is defended by anyone. > > Hiroshi seems to like it ... Probably I don't love it. Honestly I don't understand what the new TODO means exactly. I don't think this is *all* *should be* or *all or nothing* kind of thing. If a SET variable has its reason, it would behave in its own right. > However, "commit SETs even after an error" is most certainly NOT > acceptable. What I've meant is that SET commands are out of transactional control and so the word *commit SETs even after* has no meaning to me. Basically it's a user's responsisbilty to manage the errors. He only knows what's to do with the errors. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I don't think this is *all* *should be* or *all
> or nothing* kind of thing. If a SET variable has
> its reason, it would behave in its own right.
Well, we could provide some kind of escape hatch to let the behavior
vary from one variable to the next. But can you give any specific
examples? Which SET variables should not roll back on error?
regards, tom lane
Tom Lane wrote: > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > I don't think this is *all* *should be* or *all > > or nothing* kind of thing. If a SET variable has > > its reason, it would behave in its own right. > > Well, we could provide some kind of escape hatch to let the behavior > vary from one variable to the next. But can you give any specific > examples? Which SET variables should not roll back on error? It seems veeery dangerous to conclude that SET *should* roll back even if there's no *should not* roll back case. There could be no *should not* roll back case because a user could set the variable as he likes in the next transaction. Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: >Tom Lane wrote: > >>Hiroshi Inoue <Inoue@tpf.co.jp> writes: >> >>>I don't think this is *all* *should be* or *all >>>or nothing* kind of thing. If a SET variable has >>>its reason, it would behave in its own right. >>> >>Well, we could provide some kind of escape hatch to let the behavior >>vary from one variable to the next. But can you give any specific >>examples? Which SET variables should not roll back on error? >> > >It seems veeery dangerous to conclude that SET *should* >roll back even if there's no *should not* roll back case. >There could be no *should not* roll back case because >a user could set the variable as he likes in the next >transaction. > In whihc case, if I'm understanding you correctly Hiroshi-san, the rollback is moot anyway... IE BEGIN transaction_1 ... SET SOMEVAR=SOMETHING ... COMMIT (transaction_1 fails and rolls back) BEGIN transaction_2 ... SET SOMEVAR=SOMETHINGELSE ... COMMIT (transaction_2 succeeds) SOMEVAR, in either case, assuming transaction_2 succeeds, would be SOMETHINGELSE. If both succeed SOMEVAR is SOMETHINGELSE, if the first succeeds and the second fails SOMEVAR will be SOMETHING. If neither succeed SOMEVAR (for this short example) is whatever it was before the two transactions. Am I understanding you correctly in that this is the example you were trying to point out? > > >Hiroshi Inoue > http://w2422.nsk.ne.jp/~inoue/ >
Michael Loftis wrote: > > Hiroshi Inoue wrote: > > >Tom Lane wrote: > > > >>Hiroshi Inoue <Inoue@tpf.co.jp> writes: > >> > >>>I don't think this is *all* *should be* or *all > >>>or nothing* kind of thing. If a SET variable has > >>>its reason, it would behave in its own right. > >>> > >>Well, we could provide some kind of escape hatch to let the behavior > >>vary from one variable to the next. But can you give any specific > >>examples? Which SET variables should not roll back on error? > >> > > > >It seems veeery dangerous to conclude that SET *should* > >roll back even if there's no *should not* roll back case. > >There could be no *should not* roll back case because > >a user could set the variable as he likes in the next > >transaction. > > > In whihc case, if I'm understanding you correctly Hiroshi-san, the > rollback is moot anyway... > > IE > > BEGIN transaction_1 > ... > SET SOMEVAR=SOMETHING > ... > COMMIT > > (transaction_1 fails and rolls back) Probably you are misunderstanding my point. I don't think that SOMEVAR *should* be put back on failure. Users must know what value would be set to the SOMEVAR after an error. In some cases it must be put back, in some cases the current value is OK, in other cases new SOMEVAR is needed. Basically it's a user's resposibilty to set the value. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/