Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Sun, Sep 27, 2009 at 1:31 PM, Robert Haas robertmh...@gmail.com wrote: As to #3, that's obviously gotta be fixed. If we're to further consider this patch for this CommitFest, that fixing needs to happen pretty soon. Since it has been 6 days since I posted this and more than 2 weeks since the problem was found, I am moving this patch to returned with feedback. If it is resubmitted for the next CommitFest, please change the subject line to something like lock_timeout GUC so that it will match what the patch actually does. I think we have consensus that a GUC is the way to go here, and the feature seems to have enough support. Investigating a set-GUC-for-this-statement-only feature also seems to have some support, but that would be a separate patch and not necessary to satisfy the OP's use case. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Mon, Sep 21, 2009 at 6:07 AM, Boszormenyi Zoltan z...@cybertec.at wrote: Jeff Janes írta: On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at mailto:z...@cybertec.at wrote: Boszormenyi Zoltan írta: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy I disagree with Tom on this point. *If* I was trying to implement a server policy, then sure, it should not be done by embedding the timeout in the SQL statement. But I don't think they want this to implement a server policy. (And if we do, why would we thump the poor victims that are waiting on the lock, rather than the rogue who decided to take a lock and then camp out on it?) The use case for WAIT [N] is not a server policy, but a UI policy. I have two ways to do this task. The preferred way needs to lock a row, but waiting for it may take too long. So if I can't get the lock within a reasonable time, I fall back on a less-preferred but still acceptable way of doing the task, one that doesn't need the lock. If we move to a new server, the appropriate value for the time out does not change, because the appropriate level is the concern of the UI and the end users, not the database server. This wouldn't be scattered all over the application, either. In my experience, if you have an application that could benefit from this, you might have 1 or 2 uses for WAIT [N] out of 1,000+ statements in the application. (From my perspective, if there were to be a WAIT [N] option, it could plug into the statement_timeout mechanism rather than the proposed lock_timeout mechanism.) I think that if the use case for a GUC is to set it, run a single very specific statement, and then unset it, that is pretty clear evidence that this should not be a GUC in the first place. Maybe I am biased in this because I am primarily thinking about how I would use such a feature, rather than how Hans-Juergen intends to use it, and maybe those uses differ. Hans-Juergen, could you describe your use case a little bit more? Who do is going to be getting these time-out errors, the queries run by the web-app, or longer running back-office queries? And when they do get an error, what will they do about it? Our use case is to port a huge set of Informix apps, that use SET LOCK MODE TO WAIT N; Apparently Tom Lane was on the opinion that PostgreSQL won't need anything more in that regard. In case the app gets an error, the query (transaction) can be retried, the when can be user controlled. I tried to argue on the SELECT ... WAIT N part as well, but for our purposes currently the GUC is enough. Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. In addition to the previously mentioned seg-fault issues when attempting to use this feature (confirmed in another machine, linux, 64 bit, and --enable-cassert does not offer any help), I have some more concerns about the patch. From the docs: doc/src/sgml/config.sgml Abort any statement that tries to lock any rows or tables and the lock has to wait more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If varnamelog_min_error_statement/ is set to literalERROR/ or lower, the statement that timed out will also be logged. A value of zero (the default) turns off the limitation. This suggests that all row locks will have this behavior. However, my experiments show that row locks attempted to be taken for ordinary UPDATE commands do not time out. If this is only intended to apply to SELECT FOR UPDATE, that should be documented here. It is documented elsewhere that this applies to SELECT...FOR UPDATE, but it is not documented that this the only row-locks it applies to. from the time the command arrives at the server. I am pretty sure this is not the desired behavior, otherwise how does it differ from statement_timeout? I think it must be a copy and paste error for the doc. For the implementation, I think the patch touches too much code. In
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Robert Haas robertmh...@gmail.com writes: As to #1, personally, I think it's quite useful. The arguments that have been made that lock_timeout is redundant with statement_timeout don't seem to me to have much merit. ... As to #2, I was initially thinking dedicated syntax would be better because I hate SET guc = value; do thing; SET guc = previous_value;. But now I'm realizing that there's every reason to suppose that SELECT FOR UPDATE will not be the only case where we want to do this - so I think a GUC is the only reasonable choice. Yeah. I believe that a reasonable argument can be made for being able to limit lock waits separately from total execution time, but it is *not* clear to me why SELECT FOR UPDATE per-tuple waits should be the one single solitary place where that is useful. IIRC I was against the SELECT FOR UPDATE NOWAIT syntax to begin with, because of exactly this same reasoning. But that having been said, I think some kind of syntax to set a GUC for just one statement would be way useful, per discussions downthread. However, that seems like it can and should be a separate pach. Worth looking at. We do already have SET LOCAL, and the per-function GUC settings, but that may not be sufficient. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Wed, 2009-09-23 at 10:58 -0700, Josh Berkus wrote: So, while some people have asserted that a lock_timeout GUC would allow users to retrofit older applications to time out on locks, I just don't see that being the case. You'd have to refactor regardless, and if you're going to, just add the WAIT statement to the lock request. But note that almost every statement contains a lock request of some kind. So you'd need to add a WAIT clause to every single statement type in PostgreSQL. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Mon, Sep 21, 2009 at 3:07 AM, Boszormenyi Zoltan z...@cybertec.at wrote: Jeff Janes írta: Maybe I am biased in this because I am primarily thinking about how I would use such a feature, rather than how Hans-Juergen intends to use it, and maybe those uses differ. Hans-Juergen, could you describe your use case a little bit more? Who do is going to be getting these time-out errors, the queries run by the web-app, or longer running back-office queries? And when they do get an error, what will they do about it? Our use case is to port a huge set of Informix apps, that use SET LOCK MODE TO WAIT N; Apparently Tom Lane was on the opinion that PostgreSQL won't need anything more in that regard. Will statement_timeout not suffice for that use case? I understand that they will do different things, but do not understand why those difference are important. Are there invisible deadlocks that need to be timed out, while long running but not dead-locking queries that need to not be timed out? I guess re-running a long-running query is never going to succeed unless the execution plan is improved, while rerunning a long-blocking query is expected to succeed eventually? Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Jeff, Will statement_timeout not suffice for that use case? Well, currently statement_timeout doesn't affect waiting for locks. And as a DBA, I don't think I'd want the same timeout for executing queries as for waiting for a lock. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Josh Berkus j...@agliodbs.com writes: Jeff, Will statement_timeout not suffice for that use case? Well, currently statement_timeout doesn't affect waiting for locks. Sure it does. And as a DBA, I don't think I'd want the same timeout for executing queries as for waiting for a lock. Well, that's exactly what Jeff is questioning. How big is the use-case for that exactly? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom, Well, that's exactly what Jeff is questioning. How big is the use-case for that exactly? I think that it's not necessary to have a 2nd GUC, but for a different reason than argued. For the applications I work on, I tend to set statement_timeout to something high designed just to catch runaway queries, like 2min or 5min (or 1 hour on data warehouses). Partly this is because statement_timeout is so indiscriminate, and I don't want to terminate queries I actually wanted to complete. If the lock time is included in the statement_timeout counter, even more so. This would mean that I'd want a lock_timeout which was much shorter than the statement_timeout. However, I also stand by my statement that I don't think that a blanket per-server lock_timeout is that useful; you want the lock timeout to be based on how many locks you're waiting for, what the particular operation is, what the user is expecting, etc. And you need so send them a custom error message which explains the lock wait. So, while some people have asserted that a lock_timeout GUC would allow users to retrofit older applications to time out on locks, I just don't see that being the case. You'd have to refactor regardless, and if you're going to, just add the WAIT statement to the lock request. So, -1 from me on having a lock_timeout GUC for now. However, I think this is another one worth taking an informal blog poll to reach users other than hackers, yes? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Jeff, Will statement_timeout not suffice for that use case? Well, currently statement_timeout doesn't affect waiting for locks. Sure it does. And as a DBA, I don't think I'd want the same timeout for executing queries as for waiting for a lock. this is exactly the point it is simply an additional use case. while statement_timeout is perfect to kick out queries which take too long a lock_timeout serves a totally different purpose because you will get a totally different error message. imagine some old 4GL terminal application: in this case you will hardly reach a statement_timeout because you will simply want to wait until things appear on your screen. however, you definitely don't want to wait forever if somebody keeps working on some product which is on stock and never finishes. btw, this old terminal application i was talking about is exactly the usecase we had - this is why this patch has been made. we are porting roughly 2500 terminal application from informix to postgresql. we are talking about entire factory production lines and so on here (the ECPG patches posted recently are for the same project, btw.). there are countless use-cases where you want to know whether you are locked out or whether you are just taking too long - the message is totally different. the goal of the patch is to have a mechanism to make sure that you don't starve to death. as far is syntax is concerned: there are good reasons for WAIT and good reasons for a GUC. while the WAIT syntax is clearly for a very precise instruction for a very certain place in a program, a GUC is a more overall policy. i don't see a reason why we should not have both anyway. a GUC has the charm that it can be assigned to roles, procedures, etc. nicely a WAIT clause has the charm of being incredibly precise. i can see good arguments for both. the code itself is pretty simplistic - it needs no effort to be up to date and it does not harm anything else - it is pretty isolated. many thanks, hans -- Cybertec Schoenig Schoenig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Jeff Janes wrote: Will statement_timeout not suffice for that use case? we tried to get around it without actually touching the core but we really need this functionality. patching the core here is not the primary desire we have. it is all about modeling some functionality which was truly missing. many thanks, hans -- Cybertec Schoenig Schoenig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Jeff Janes írta: On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at mailto:z...@cybertec.at wrote: Boszormenyi Zoltan írta: Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. I'm getting segfaults, built in 32 bit linux with gcc bin/pg_ctl -D data start -l logfile -o --lock_timeout=5 Session 1: jjanes=# begin; BEGIN jjanes=# select * from pgbench_branches where bid=3 for update; bid | bbalance | filler -+--+ 3 | -3108950 | (1 row) Session 2: jjanes=# select * from pgbench_branches where bid=3 for update; ERROR: could not obtain lock on row in relation pgbench_branches jjanes=# select * from pgbench_branches where bid=3 for update; ERROR: could not obtain lock on row in relation pgbench_branches jjanes=# select * from pgbench_branches where bid=3 for update; ERROR: could not obtain lock on row in relation pgbench_branches jjanes=# set lock_timeout = 0 ; SET jjanes=# select * from pgbench_branches where bid=3 for update; Session 2 is now blocked Session1: jjanes=# commit; long pause server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. I just realized I should have built with asserts turned on. I'll do that tomorrow, but don't want to delay this info until then, so I am sending it now. Cheers, Jeff Thanks for the test. The same test worked perfectly at the time I posted it and it also works perfectly on 8.4.1 *now*. So something has changed between then and the current CVS, because I was able to reproduce the segfault with the current CVS HEAD. We'll have to update the patch obviously... Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Jeff Janes írta: On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at mailto:z...@cybertec.at wrote: Boszormenyi Zoltan írta: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy I disagree with Tom on this point. *If* I was trying to implement a server policy, then sure, it should not be done by embedding the timeout in the SQL statement. But I don't think they want this to implement a server policy. (And if we do, why would we thump the poor victims that are waiting on the lock, rather than the rogue who decided to take a lock and then camp out on it?) The use case for WAIT [N] is not a server policy, but a UI policy. I have two ways to do this task. The preferred way needs to lock a row, but waiting for it may take too long. So if I can't get the lock within a reasonable time, I fall back on a less-preferred but still acceptable way of doing the task, one that doesn't need the lock. If we move to a new server, the appropriate value for the time out does not change, because the appropriate level is the concern of the UI and the end users, not the database server. This wouldn't be scattered all over the application, either. In my experience, if you have an application that could benefit from this, you might have 1 or 2 uses for WAIT [N] out of 1,000+ statements in the application. (From my perspective, if there were to be a WAIT [N] option, it could plug into the statement_timeout mechanism rather than the proposed lock_timeout mechanism.) I think that if the use case for a GUC is to set it, run a single very specific statement, and then unset it, that is pretty clear evidence that this should not be a GUC in the first place. Maybe I am biased in this because I am primarily thinking about how I would use such a feature, rather than how Hans-Juergen intends to use it, and maybe those uses differ. Hans-Juergen, could you describe your use case a little bit more? Who do is going to be getting these time-out errors, the queries run by the web-app, or longer running back-office queries? And when they do get an error, what will they do about it? Our use case is to port a huge set of Informix apps, that use SET LOCK MODE TO WAIT N; Apparently Tom Lane was on the opinion that PostgreSQL won't need anything more in that regard. In case the app gets an error, the query (transaction) can be retried, the when can be user controlled. I tried to argue on the SELECT ... WAIT N part as well, but for our purposes currently the GUC is enough. Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. In addition to the previously mentioned seg-fault issues when attempting to use this feature (confirmed in another machine, linux, 64 bit, and --enable-cassert does not offer any help), I have some more concerns about the patch. From the docs: doc/src/sgml/config.sgml Abort any statement that tries to lock any rows or tables and the lock has to wait more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If varnamelog_min_error_statement/ is set to literalERROR/ or lower, the statement that timed out will also be logged. A value of zero (the default) turns off the limitation. This suggests that all row locks will have this behavior. However, my experiments show that row locks attempted to be taken for ordinary UPDATE commands do not time out. If this is only intended to apply to SELECT FOR UPDATE, that should be documented here. It is documented elsewhere that this applies to SELECT...FOR UPDATE, but it is not documented that this the only row-locks it applies to. from the time the command arrives at the server. I am pretty sure this is not the desired behavior, otherwise how does it differ from statement_timeout? I think it must be a copy and paste error for the doc. For the implementation, I think the patch touches too much code. In particular, lwlock.c. Is the time spent waiting on ProcArrayLock significant
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
I think that if the use case for a GUC is to set it, run a single very specific statement, and then unset it, that is pretty clear evidence that this should not be a GUC in the first place. +1 Plus, do we really want another GUC? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Mon, Sep 21, 2009 at 1:32 PM, Josh Berkus j...@agliodbs.com wrote: I think that if the use case for a GUC is to set it, run a single very specific statement, and then unset it, that is pretty clear evidence that this should not be a GUC in the first place. +1 Plus, do we really want another GUC? Well, I don't share the seemingly-popular sentiment that more GUCs are a bad thing. GUCs let you change important parameters of the application without compiling, which is very useful. Of course, I don't want: - GUCs that I'm going to set, execute one statement, and the unset (and this likely falls into that category). - GUCs that are poorly designed so that it's not clear, even to an experienced user, what value to set. - GUCs that exist only to work around the inability of the database to figure out the appropriate value without user input. On the flip side, rereading the thread, one major advantage of the GUC is that it can be used for statements other than SELECT, which hard-coded syntax can't. That might be enough to make me change my vote. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Robert Haas escribió: Of course, I don't want: - GUCs that I'm going to set, execute one statement, and the unset (and this likely falls into that category). - GUCs that are poorly designed so that it's not clear, even to an experienced user, what value to set. - GUCs that exist only to work around the inability of the database to figure out the appropriate value without user input. On the flip side, rereading the thread, one major advantage of the GUC is that it can be used for statements other than SELECT, which hard-coded syntax can't. That might be enough to make me change my vote. Perhaps we'd benefit from a way to set a variable for a single query; something like WITH ( SET query_lock_timeout = 5s ) SELECT ... Of course, this particular syntax doesn't work because WITH is already taken. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Mon, Sep 21, 2009 at 3:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Robert Haas escribió: Of course, I don't want: - GUCs that I'm going to set, execute one statement, and the unset (and this likely falls into that category). - GUCs that are poorly designed so that it's not clear, even to an experienced user, what value to set. - GUCs that exist only to work around the inability of the database to figure out the appropriate value without user input. On the flip side, rereading the thread, one major advantage of the GUC is that it can be used for statements other than SELECT, which hard-coded syntax can't. That might be enough to make me change my vote. Perhaps we'd benefit from a way to set a variable for a single query; something like WITH ( SET query_lock_timeout = 5s ) SELECT ... Of course, this particular syntax doesn't work because WITH is already taken. Yeah, I thought about that. I think that would be sweet. Maybe LET (query_lock_timeout = 5 s) IN SELECT ... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Alvaro Herrera alvhe...@commandprompt.com writes: Perhaps we'd benefit from a way to set a variable for a single query; Yeah, particularly if it allows us to fend off requests for random one-off features to accomplish the same thing ... WITH ( SET query_lock_timeout = 5s ) SELECT ... Of course, this particular syntax doesn't work because WITH is already taken. I think you could make it work if you really wanted, but perhaps a different keyword would be better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at wrote: Boszormenyi Zoltan írta: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy I disagree with Tom on this point. *If* I was trying to implement a server policy, then sure, it should not be done by embedding the timeout in the SQL statement. But I don't think they want this to implement a server policy. (And if we do, why would we thump the poor victims that are waiting on the lock, rather than the rogue who decided to take a lock and then camp out on it?) The use case for WAIT [N] is not a server policy, but a UI policy. I have two ways to do this task. The preferred way needs to lock a row, but waiting for it may take too long. So if I can't get the lock within a reasonable time, I fall back on a less-preferred but still acceptable way of doing the task, one that doesn't need the lock. If we move to a new server, the appropriate value for the time out does not change, because the appropriate level is the concern of the UI and the end users, not the database server. This wouldn't be scattered all over the application, either. In my experience, if you have an application that could benefit from this, you might have 1 or 2 uses for WAIT [N] out of 1,000+ statements in the application. (From my perspective, if there were to be a WAIT [N] option, it could plug into the statement_timeout mechanism rather than the proposed lock_timeout mechanism.) I think that if the use case for a GUC is to set it, run a single very specific statement, and then unset it, that is pretty clear evidence that this should not be a GUC in the first place. Maybe I am biased in this because I am primarily thinking about how I would use such a feature, rather than how Hans-Juergen intends to use it, and maybe those uses differ. Hans-Juergen, could you describe your use case a little bit more? Who do is going to be getting these time-out errors, the queries run by the web-app, or longer running back-office queries? And when they do get an error, what will they do about it? Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. In addition to the previously mentioned seg-fault issues when attempting to use this feature (confirmed in another machine, linux, 64 bit, and --enable-cassert does not offer any help), I have some more concerns about the patch. From the docs: doc/src/sgml/config.sgml Abort any statement that tries to lock any rows or tables and the lock has to wait more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If varnamelog_min_error_statement/ is set to literalERROR/ or lower, the statement that timed out will also be logged. A value of zero (the default) turns off the limitation. This suggests that all row locks will have this behavior. However, my experiments show that row locks attempted to be taken for ordinary UPDATE commands do not time out. If this is only intended to apply to SELECT FOR UPDATE, that should be documented here. It is documented elsewhere that this applies to SELECT...FOR UPDATE, but it is not documented that this the only row-locks it applies to. from the time the command arrives at the server. I am pretty sure this is not the desired behavior, otherwise how does it differ from statement_timeout? I think it must be a copy and paste error for the doc. For the implementation, I think the patch touches too much code. In particular, lwlock.c. Is the time spent waiting on ProcArrayLock significant enough that it needs all of that code to support timing it out? I don't think it should ever take more than a few microseconds to obtain that light-weight lock. And if we do want to time all of the light weight access, shouldn't those times be summed up, rather than timing out only if any single one of them exceeds the threshold in isolation? (That is my interpretation of how the code works currently, I could be wrong on that.) If the seg-faults are fixed, I am still skeptical that this patch is acceptable, because the problem it solves seems to be poorly or incompletely specified. Cheers, Jeff
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Sat, Sep 19, 2009 at 4:17 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at wrote: Boszormenyi Zoltan írta: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy I disagree with Tom on this point. *If* I was trying to implement a server policy, then sure, it should not be done by embedding the timeout in the SQL statement. But I don't think they want this to implement a server policy. (And if we do, why would we thump the poor victims that are waiting on the lock, rather than the rogue who decided to take a lock and then camp out on it?) The use case for WAIT [N] is not a server policy, but a UI policy. I have two ways to do this task. The preferred way needs to lock a row, but waiting for it may take too long. So if I can't get the lock within a reasonable time, I fall back on a less-preferred but still acceptable way of doing the task, one that doesn't need the lock. If we move to a new server, the appropriate value for the time out does not change, because the appropriate level is the concern of the UI and the end users, not the database server. This wouldn't be scattered all over the application, either. In my experience, if you have an application that could benefit from this, you might have 1 or 2 uses for WAIT [N] out of 1,000+ statements in the application. (From my perspective, if there were to be a WAIT [N] option, it could plug into the statement_timeout mechanism rather than the proposed lock_timeout mechanism.) I think that if the use case for a GUC is to set it, run a single very specific statement, and then unset it, that is pretty clear evidence that this should not be a GUC in the first place. +1 to all of the above. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at wrote: Boszormenyi Zoltan írta: Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. I'm getting segfaults, built in 32 bit linux with gcc bin/pg_ctl -D data start -l logfile -o --lock_timeout=5 Session 1: jjanes=# begin; BEGIN jjanes=# select * from pgbench_branches where bid=3 for update; bid | bbalance | filler -+--+ 3 | -3108950 | (1 row) Session 2: jjanes=# select * from pgbench_branches where bid=3 for update; ERROR: could not obtain lock on row in relation pgbench_branches jjanes=# select * from pgbench_branches where bid=3 for update; ERROR: could not obtain lock on row in relation pgbench_branches jjanes=# select * from pgbench_branches where bid=3 for update; ERROR: could not obtain lock on row in relation pgbench_branches jjanes=# set lock_timeout = 0 ; SET jjanes=# select * from pgbench_branches where bid=3 for update; Session 2 is now blocked Session1: jjanes=# commit; long pause server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. I just realized I should have built with asserts turned on. I'll do that tomorrow, but don't want to delay this info until then, so I am sending it now. Cheers, Jeff
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan írta: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ diff -dcrpN pgsql.ooscur/doc/src/sgml/config.sgml pgsql.locktimeout/doc/src/sgml/config.sgml *** pgsql.ooscur/doc/src/sgml/config.sgml 2009-08-26 10:19:48.0 +0200 --- pgsql.locktimeout/doc/src/sgml/config.sgml 2009-09-03 15:41:34.0 +0200 *** COPY postgres_log FROM '/full/path/to/lo *** 4028,4033 --- 4028,4056 /listitem /varlistentry + varlistentry id=guc-lock-timeout xreflabel=lock_timeout + termvarnamelock_timeout/varname (typeinteger/type)/term + indexterm +primaryvarnamelock_timeout/ configuration parameter/primary + /indexterm + listitem +para + Abort any statement that tries to lock any rows or tables and the lock + has to wait more than the specified number of milliseconds, starting + from the time the command arrives at the server from the client. + If varnamelog_min_error_statement/ is set to literalERROR/ or + lower, the statement that timed out will also be logged. + A value of zero (the default) turns off the limitation. +/para + +para + Setting varnamelock_timeout/ in + filenamepostgresql.conf/ is not recommended because it + affects all sessions. +/para + /listitem + /varlistentry + varlistentry id=guc-vacuum-freeze-table-age xreflabel=vacuum_freeze_table_age termvarnamevacuum_freeze_table_age/varname (typeinteger/type)/term indexterm diff -dcrpN pgsql.ooscur/doc/src/sgml/ref/lock.sgml pgsql.locktimeout/doc/src/sgml/ref/lock.sgml *** pgsql.ooscur/doc/src/sgml/ref/lock.sgml 2009-01-16 11:44:56.0 +0100 --- pgsql.locktimeout/doc/src/sgml/ref/lock.sgml 2009-09-03 15:41:34.0 +0200 *** where replaceable class=PARAMETERloc *** 39,46 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. Once obtained, the lock is held for the !remainder of the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para --- 39,49 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. If varnamelock_timeout/varname is set to a value !higher than 0, and the lock cannot be acquired under the specified !timeout value in milliseconds, the command is aborted and an error !is emitted. Once obtained, the lock is held for the remainder of !the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para diff -dcrpN pgsql.ooscur/doc/src/sgml/ref/select.sgml pgsql.locktimeout/doc/src/sgml/ref/select.sgml *** pgsql.ooscur/doc/src/sgml/ref/select.sgml 2009-08-31 12:55:43.0 +0200 --- pgsql.locktimeout/doc/src/sgml/ref/select.sgml 2009-09-03 15:41:34.0 +0200 *** FOR SHARE [ OF replaceable class=param *** 1109,1114 --- 1109,1122 /para para + If literalNOWAIT/ option is not specified and varnamelock_timeout/varname + is set to a value higher than 0, and the lock needs to wait more than + the specified value in milliseconds, the command reports an error after + timing out, rather than waiting indefinitely. The note in the previous + paragraph applies to the varnamelock_timeout/varname, too. +/para + +para literalFOR SHARE/literal behaves
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ diff -dcrpN pgsql.orig/doc/src/sgml/config.sgml pgsql/doc/src/sgml/config.sgml *** pgsql.orig/doc/src/sgml/config.sgml 2009-07-17 07:50:48.0 +0200 --- pgsql/doc/src/sgml/config.sgml 2009-07-30 13:12:07.0 +0200 *** COPY postgres_log FROM '/full/path/to/lo *** 4018,4023 --- 4018,4046 /listitem /varlistentry + varlistentry id=guc-lock-timeout xreflabel=lock_timeout + termvarnamelock_timeout/varname (typeinteger/type)/term + indexterm +primaryvarnamelock_timeout/ configuration parameter/primary + /indexterm + listitem +para + Abort any statement that tries to lock any rows or tables and the lock + has to wait more than the specified number of milliseconds, starting + from the time the command arrives at the server from the client. + If varnamelog_min_error_statement/ is set to literalERROR/ or + lower, the statement that timed out will also be logged. + A value of zero (the default) turns off the limitation. +/para + +para + Setting varnamelock_timeout/ in + filenamepostgresql.conf/ is not recommended because it + affects all sessions. +/para + /listitem + /varlistentry + varlistentry id=guc-vacuum-freeze-table-age xreflabel=vacuum_freeze_table_age termvarnamevacuum_freeze_table_age/varname (typeinteger/type)/term indexterm diff -dcrpN pgsql.orig/doc/src/sgml/ref/lock.sgml pgsql/doc/src/sgml/ref/lock.sgml *** pgsql.orig/doc/src/sgml/ref/lock.sgml 2009-01-16 11:44:56.0 +0100 --- pgsql/doc/src/sgml/ref/lock.sgml 2009-07-30 13:29:07.0 +0200 *** where replaceable class=PARAMETERloc *** 39,46 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. Once obtained, the lock is held for the !remainder of the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para --- 39,49 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. If varnamelock_timeout/varname is set to a value !higher than 0, and the lock cannot be acquired under the specified !timeout value in milliseconds, the command is aborted and an error !is emitted. Once obtained, the lock is held for the remainder of !the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para diff -dcrpN pgsql.orig/doc/src/sgml/ref/select.sgml pgsql/doc/src/sgml/ref/select.sgml *** pgsql.orig/doc/src/sgml/ref/select.sgml 2009-05-04 11:00:49.0 +0200 --- pgsql/doc/src/sgml/ref/select.sgml 2009-07-30 13:36:57.0 +0200 *** FOR SHARE [ OF replaceable class=param *** 1101,1106 --- 1101,1114 /para para + If literalNOWAIT/ option is not specified and varnamelock_timeout/varname + is set to a value higher than 0, and the lock needs to wait more than + the specified value in milliseconds, the command reports an error after + timing out, rather than waiting indefinitely. The note in the previous + paragraph applies to the varnamelock_timeout/varname, too. +/para + +para literalFOR SHARE/literal behaves similarly, except that it acquires a shared rather than exclusive lock on each retrieved row. A shared lock blocks other transactions from performing diff -dcrpN pgsql.orig/src/backend/access/heap/heapam.c
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Bruce Momjian írta: Hans-Juergen Schoenig wrote: hello everybody, from my side the goal of this discussion is to extract a consensus so that we can go ahead and implement this issue for 8.5. our customer here needs a solution to this problem and we have to come up with something which can then make it into PostgreSQL core. how shall we proceed with the decision finding process here? i am fine with a GUC and with an grammar extension - i just need a decision which stays unchanged. Do we have answer for Hans-Juergen here? Do we? The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. Can we go ahead implementing it? I have added a vague TODO: Consider a lock timeout parameter * http://archives.postgresql.org/pgsql-hackers/2009-05/msg00485.php -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. Syntax consistency with NOWAIT? -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan wrote: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. Syntax consistency with NOWAIT? Consistency could also be achieved by removing NOWAIT, but I don't see you proposing that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Alvaro Herrera írta: Boszormenyi Zoltan wrote: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. Syntax consistency with NOWAIT? And easy of use in diverging from default lock_timeout? Consistency could also be achieved by removing NOWAIT, but I don't see you proposing that. And you won't see me proposing any other feature removal either :-) -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Hans-Juergen Schoenig wrote: hello everybody, from my side the goal of this discussion is to extract a consensus so that we can go ahead and implement this issue for 8.5. our customer here needs a solution to this problem and we have to come up with something which can then make it into PostgreSQL core. how shall we proceed with the decision finding process here? i am fine with a GUC and with an grammar extension - i just need a decision which stays unchanged. Do we have answer for Hans-Juergen here? I have added a vague TODO: Consider a lock timeout parameter * http://archives.postgresql.org/pgsql-hackers/2009-05/msg00485.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello everybody, from my side the goal of this discussion is to extract a consensus so that we can go ahead and implement this issue for 8.5. our customer here needs a solution to this problem and we have to come up with something which can then make it into PostgreSQL core. how shall we proceed with the decision finding process here? i am fine with a GUC and with an grammar extension - i just need a decision which stays unchanged. comments and votes are welcome. many thanks, hans -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Can't you to this today with statement_timeout? Surely you do want to rollback the whole transaction or at least the subtransaction if you have error handling. -- Greg On 11 May 2009, at 10:26, Hans-Juergen Schoenig postg...@cybertec.at wrote: hello everybody, i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. i would like to add this functionality to PostgreSQL 8.5. the oracle syntax is quite clear and easy to use here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2126016 informix should behave pretty much the same way. are there any arguments from hackers' side against this feature? many thanks, hans -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello greg, the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; this practically means 3 commands. the killer argument, however, is that the lock might very well happen ways after the statement has started. imagine something like that (theoretical example): SELECT ... FROM WHERE x ( SELECT some_very_long_thing) FOR UPDATE ...; some operation could run for ages without ever taking a single, relevant lock here. so, you don't really get the same thing with statement_timeout. regards, hans Greg Stark wrote: Can't you to this today with statement_timeout? Surely you do want to rollback the whole transaction or at least the subtransaction if you have error handling. -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Hans-Juergen Schoenig postg...@cybertec.at the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; Why not extend the SET instruction to allow configuration parameters to be set only in the duration of the transaction or the next n commands? -- Lucas Brito
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
--On 11. Mai 2009 06:38:44 -0300 Lucas Brito luca...@gmail.com wrote: Why not extend the SET instruction to allow configuration parameters to be set only in the duration of the transaction or the next n commands? It's already there: see SET LOCAL. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
-- Greg On 11 May 2009, at 11:18, Hans-Juergen Schoenig postg...@cybertec.at wrote: hello greg, the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; this practically means 3 commands. I tend to think there should be protocol level support for options like this but that would require buy-in from the interface writers. the killer argument, however, is that the lock might very well happen ways after the statement has started. Sure. But Isn't the statement_timeout behaviour what an application writer would actually want? Why would he care how long some sub-part of the statement took? Isn't an application -you used the example of a web app - really concerned with its response time? imagine something like that (theoretical example): SELECT ... FROM WHERE x ( SELECT some_very_long_thing) FOR UPDATE ...; some operation could run for ages without ever taking a single, relevant lock here. so, you don't really get the same thing with statement_timeout. regards, hans Greg Stark wrote: Can't you to this today with statement_timeout? Surely you do want to rollback the whole transaction or at least the subtransaction if you have error handling. -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
I tend to think there should be protocol level support for options like this but that would require buy-in from the interface writers. how would you do it? if you support it on the protocol level, you still need a way to allow the user to tell you how ... i would see WAIT for DELETE, UPDATE and SELECT FOR UPDATE. did you have more in mind? the killer argument, however, is that the lock might very well happen ways after the statement has started. Sure. But Isn't the statement_timeout behaviour what an application writer would actually want? Why would he care how long some sub-part of the statement took? Isn't an application -you used the example of a web app - really concerned with its response time? no, for a simple reason: in this case you would depend ways too much in other tasks. some other reads which just pump up the load or some nightly cronjobs would give you timeouts which are not necessarily related to locking. we really want to protect us against some LOCK TABLE IN ACCESS EXCLUSIVE MODE - i am not looking for a solution which kills queries after some time (we have that already). i want protect myself against locking issues. this feature is basically supported by most big vendor (informix, oracle, just to name a few). i am proposing this because i have needed it for a long time already and in this case it is also needed for a migration project. hans -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Hans-Juergen Schoenig postg...@cybertec.at writes: i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. I guess my immediate reactions to this are: 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks? 2. That clear and easy to use oracle syntax sucks. You do not want to be embedding lock timeout constants in your application queries. When you move to a new server and the appropriate timeout changes, do you want to be trying to update your clients for that? What I think has been proposed previously is a GUC variable named something like lock_timeout, which would cause a wait for *any* heavyweight lock to abort after such-and-such an interval. This would address your point about not wanting to use an overall statement_timeout, and it would be more general than a feature that only works for SELECT FOR UPDATE row locks, and it would allow decoupling the exact length of the timeout from application query logic. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan z...@cybertec.at writes: Would the lock_timeout work for all to be acquired locks individually, or all of them combined for the statement? The individual application of the timeout for every locks individually wouldn't be too nice. I think the way you're describing would be both harder to implement and full of its own strange traps. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom Lane írta:
Boszormenyi Zoltan z...@cybertec.at writes:
Would the lock_timeout work for all to be acquired locks individually,
or all of them combined for the statement? The individual application
of the timeout for every locks individually wouldn't be too nice.
I think the way you're describing would be both harder to implement
and full of its own strange traps.
Why?
PGSemaphoreTimedLock(..., struct timespec *timeout)
{
...
gettimeofday(tv1, NULL);
semtimedop(... , timeout);
gettimeofday(tv2, NULL);
decrease *timeout with the difference of tv1 and tv2
}
Next call will use the decreased value.
Either all locks are acquired in the given time, or the next try will
timeout (error) or there are still locks and the timeout went down to
or below zero (error). Why is it hard?
regards, tom lane
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan z...@cybertec.at writes: Tom Lane írta: I think the way you're describing would be both harder to implement and full of its own strange traps. Why? Well, for one thing: if I roll back a subtransaction, should the lock wait time it used now no longer count against the total? If not, once a timeout failure has occurred it'll no longer be possible for the total transaction to do anything, even if it rolls back a failed subtransaction. But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Hi, Tom Lane írta: Hans-Juergen Schoenig postg...@cybertec.at writes: i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. I guess my immediate reactions to this are: 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks? 2. That clear and easy to use oracle syntax sucks. You do not want to be embedding lock timeout constants in your application queries. When you move to a new server and the appropriate timeout changes, do you want to be trying to update your clients for that? What I think has been proposed previously is a GUC variable named something like lock_timeout, which would cause a wait for *any* heavyweight lock to abort after such-and-such an interval. This would address your point about not wanting to use an overall statement_timeout, and it would be more general than a feature that only works for SELECT FOR UPDATE row locks, and it would allow decoupling the exact length of the timeout from application query logic. Would the lock_timeout work for all to be acquired locks individually, or all of them combined for the statement? The individual application of the timeout for every locks individually wouldn't be too nice. E.g. SELECT ... FOR ... WAIT N (N in seconds) behaviour in this scenario below is not what the application writed would expect: xact 1: SELECT ... FOR UPDATE (record 1) xact 2: SELECT ... FOR UPDATE (record 2) xact 3: SELECT ... FOR UPDATE WAIT 10 (record 1 and 2, waits for both records sequentially) xact 1: COMMIT/ROLLBACK almost 10 seconds later xact 3 acquires lock for record 1, wait for lock on record2 xact 2: COMMIT/ROLLBACK almost 10 seconds later xact 3 acquires lock for record 2 3rd transaction has to wait for almost 2 times the specified time. E.g. in Informix the SET LOCK MODE TO WAIT N works for all to-be acquired locks combined. If lock_timeout and/or ... FOR lockmode WAIT N ever gets implemented, it should behave that way. Best regards, Zoltán Böszörményi regards, tom lane -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: Tom Lane írta: I think the way you're describing would be both harder to implement and full of its own strange traps. Why? Well, for one thing: if I roll back a subtransaction, should the lock wait time it used now no longer count against the total? Does statement_timeout counts against subtransactions as well? No. If a statement finishes before statement_timeout, does it also decrease the possible runtime for the next statement? No. I was talking about locks acquired during one statement. If not, once a timeout failure has occurred it'll no longer be possible for the total transaction to do anything, even if it rolls back a failed subtransaction. But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. He argued about he wants a timeout *independent* from statement_timeout for locks only inside the same statement IIRC. regards, tom lane -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Boszormenyi Zoltan z...@cybertec.at: Does statement_timeout counts against subtransactions as well? No. If a statement finishes before statement_timeout, does it also decrease the possible runtime for the next statement? No. I was talking about locks acquired during one statement. With respect I can't figure out what you're trying to say here. He argued about he wants a timeout *independent* from statement_timeout for locks only inside the same statement IIRC. I think what you're saying is you think he only wanted to distinguish total time spent waiting for locks from total time spent executing including such things as i/o wait time. That's possible, Hans-Juergen wasn't very clear on what locking issues he was concerned about. I can think of a few categories of locking issues that might be problems though: 1) A web application wants to ensure that a slow batch job which locks records doesn't impact responsiveness. I think statement_timeout handles this better though. 2) A batch job might want to ensure it's still making progress even if slowly, but some other jobs might block indefinitely while holding locks (for example an email generating script might be stuck waiting for remote sites to respond). statement_timeout is better for ensuring overall execution speed but it won't fire until the entire time allotment is used up whereas something which detects being stuck on an individual lock would detect the problem much earlier (and perhaps the rest of the job could still be completed). 3) Applications which have hidden deadlocks because they block each other outside the database while holding locks in the database. This can be dealt with by using userlocks to represent the external resources but that depends on all of those external resources being identified correctly. A lock timeout would be an imprecise way to detect possible deadlocks even though it's always possible it just didn't wait long enough. Hans-Juergen, are any of these use cases good descriptions of your intended use? Or do you have a different case? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello tom ... the reason for SELECT FOR UPDATE is very simple: this is the typical lock obtained by basically every business application if written properly (updating a product, whatever). the problem with NOWAIT basically is that if a small transaction holds a a lock for a subsecond, you will already lose your transaction because it does not wait at all (which is exactly what you want in some cases). however, in many cases you want to compromise on wait forever vs. die instantly. depending on the code path we could decide how long to wait for which operation. this makes sense as we would only fire 1 statement instead of 3 (set, run, set back). i agree that a GUC is definitely an option. however, i would say that adding an extension to SELECT FOR UPDATE, UPDATE and DELETE would make more sense form a usability point of view (just my 0.02 cents). if hackers' decides to go for a GUC, we are fine as well and we will add it to 8.5. many thanks, hans On May 11, 2009, at 4:46 PM, Tom Lane wrote: Hans-Juergen Schoenig postg...@cybertec.at writes: i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. I guess my immediate reactions to this are: 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks? 2. That clear and easy to use oracle syntax sucks. You do not want to be embedding lock timeout constants in your application queries. When you move to a new server and the appropriate timeout changes, do you want to be trying to update your clients for that? What I think has been proposed previously is a GUC variable named something like lock_timeout, which would cause a wait for *any* heavyweight lock to abort after such-and-such an interval. This would address your point about not wanting to use an overall statement_timeout, and it would be more general than a feature that only works for SELECT FOR UPDATE row locks, and it would allow decoupling the exact length of the timeout from application query logic. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Greg Stark írta: 2009/5/11 Boszormenyi Zoltan z...@cybertec.at: Does statement_timeout counts against subtransactions as well? No. If a statement finishes before statement_timeout, does it also decrease the possible runtime for the next statement? No. I was talking about locks acquired during one statement. With respect I can't figure out what you're trying to say here. Sorry, bad rhetorics. Point correctly made is below. He argued about he wants a timeout *independent* from statement_timeout for locks only inside the same statement IIRC. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Hans-Jürgen Schönig postg...@cybertec.at: i agree that a GUC is definitely an option. however, i would say that adding an extension to SELECT FOR UPDATE, UPDATE and DELETE would make more sense form a usability point of view (just my 0.02 cents). I kinda agree with this. I believe Tom was arguing upthread that any change of this short should touch all of the places where NOWAIT is accepted now, and I agree with that. But having to issue SET as a separate statement and then maybe do another SET afterward to get the old value back doesn't seem like it provides any real advantage. GUCs are good for properties that you want to set and leave set, not so good for things that are associated with particular statements. It also seems to me that there's no reason for NOWAIT to be part of the syntax, but WAIT n to be a GUC. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. I agree with Tom here; I want to wait for a specific amount of time for a specific lock request. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Josh Berkus írta: But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. I agree with Tom here; I want to wait for a specific amount of time for a specific lock request. Well, thinking about it a bit more, I think we can live with that. The use case would be mostly 1 record per SELECT FOR UPDATE WAIT N query, so for this the two semantics are equal. We would differ from Informix when one SELECT fetches more than one record obviously. We can have both GUC and the SQL extension for temporary setting. SET lock_timeout = N; -- 0 means infinite? or: SET lock_timeout = infinite; NOWAIT | WAIT (or no keyword as of now) for infinite waiting | WAIT DEFAULT | WAIT N (N seconds timeout) Comments? -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Robert Haas robertmh...@gmail.com writes: I kinda agree with this. I believe Tom was arguing upthread that any change of this short should touch all of the places where NOWAIT is accepted now, and I agree with that. But having to issue SET as a separate statement and then maybe do another SET afterward to get the old value back doesn't seem like it provides any real advantage. GUCs are good for properties that you want to set and leave set, not so good for things that are associated with particular statements. My point is that I don't believe the scenario where you say that you know exactly how long each different statement in your application should wait and they should all be different. What I do find credible is that you want to set a policy for all the lock timeouts. Now think about what happens when it's time to change the policy. A GUC is gonna be a lot easier to manage than timeouts that are embedded in all your individual queries. It also seems to me that there's no reason for NOWAIT to be part of the syntax, but WAIT n to be a GUC. I wasn't happy about NOWAIT in the syntax, either ;-) ... but at least that's a boolean and not a parameter whose specific value was plucked out of thin air, which is what it's pretty much always going to be. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom, My point is that I don't believe the scenario where you say that you know exactly how long each different statement in your application should wait and they should all be different. What I do find credible is that you want to set a policy for all the lock timeouts. Now think about what happens when it's time to change the policy. A GUC is gonna be a lot easier to manage than timeouts that are embedded in all your individual queries. For production applications, it's credible that you're going to desire three different behaviors for different locks: you'll want to not wait at all for some locks, wait a limited time for others, and for a few wait forever. I agree that the time for the 2nd case wouldn't vary per lock in any reasonable case. I can see Zoltan's argument: for web applications, it's important to keep the *total* wait time under 50 seconds for most users (default browser timeout for most is 60 seconds). So it would certainly be nice if we could somehow set total wait time instead of individual operation wait time. It's also completely and totally unworkable on the database layer for multiple reasons, so I'm not going to bother pushing any idea which implements this. So, I can see having a session-based lock_timeout GUC, and also a NOWAIT statement. It would mean that users would need to set lock_timeout=-1 if they didn't want the lock to timeout, but that's consistent with how other timeouts behave. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Josh Berkus j...@agliodbs.com writes: I can see Zoltan's argument: for web applications, it's important to keep the *total* wait time under 50 seconds for most users (default browser timeout for most is 60 seconds). And why is that only about lock wait time and not about total execution time? I still think statement_timeout covers the need, or at least is close enough that it isn't justified to make lock_timeout act like that (thus making it not serve the other class of requirement). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On 5/11/09 4:25 PM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: I can see Zoltan's argument: for web applications, it's important to keep the *total* wait time under 50 seconds for most users (default browser timeout for most is 60 seconds). And why is that only about lock wait time and not about total execution time? I still think statement_timeout covers the need, or at least is close enough that it isn't justified to make lock_timeout act like that (thus making it not serve the other class of requirement). That was one of the reasons it's completely and totally unworkable, as I mentioned, if you read the next sentence. The only real answer to the response time issue is to measure total response time in the middleware. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
