AW: [HACKERS] timeout on lock feature
The only way PG could apply reasonable timeouts would be for the application to dictate them, That is exactly what we are talking about here. No. You wrote elsewhere that the application sets "30 seconds" and leaves it. But that 30 seconds doesn't have any application-level meaning In interactive OLTP it does. -- an operation could take twelve hours without tripping your 30-second timeout. Not in OLTP. Using the feature for a batch client with a low timeout would be plain wrong. What might be a reasonable alternative would be a BEGIN timeout: report failure as soon as possible after N seconds unless the timer is reset, such as by a commit. Such a timeout would be meaningful at the database-interface level. It could serve as a useful building block for application-level timeouts when the client environment has trouble applying timeouts on its own. I like that, but I do not think it is feasible. I do not think that you can guarantee an answer within x seconds, be it positive or negative. But that is what this feature would imply. If the client needs to react within x sec there is no way around implementing this in the client (there could be all kinds of trouble between client and backend). On a very busy server (in OLTP that is the only real reason your query takes too long other than waiting for a lock) you will produce still more work with this feature. That is also partly why I think that a lock timeout feature really makes sense for interactive OLTP clients. It is not a perfect solution, but it usually serves the purpose very well and keeps the client simple. And I do not agree, that it is an objective to keep the db code simple at the cost of making a standard client more complex. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: AW: [HACKERS] timeout on lock feature
Zeugswetter Andreas SB [EMAIL PROTECTED] writes: It is not something that makes anything unrelyable or less robust. How can you argue that? The presence of a lock timeout *will* make operations fail that otherwise would have succeeded; moreover that failure will be pretty unpredictable (at least from the point of view of the application that issued the command). That qualifies as "unreliable and not robust" in my book. A persistent SET variable also opens up the risk of completely unwanted failures in critical operations --- all you have to do is forget to reset the variable when the effect is no longer wanted. (Murphy's Law guarantees that you won't find out such a mistake until the worst possible time. That's even less robust.) The only way PG could apply reasonable timeouts would be for the application to dictate them, That is exactly what we are talking about here. The *real* problem is that the application cannot determine reasonable timeouts either. Perhaps the app can decide how long it is willing to wait overall, but how can it translate that into the low-level notion of an appropriate lock timeout? It does not know how many locks might get locked in the course of a query, nor which locks they are exactly, nor what the likely distribution of wait intervals is for those locks. Given that, using a lock timeout "feature" is just a crapshoot. If you say "set lock timeout X", you have no real idea how that translates to application-visible performance nor how big a risk you are taking of inducing unwanted failures. You don't even get to average out the uncertainty across a whole query, because if any one of the lock waits exceeds X, your query blows up. Yet making X large destroys the usefulness of the feature entirely, so there will always be a strong temptation to set it too low. This is the real reason why I've been holding out for restricting the feature to a specific LOCK TABLE statement: if it's designed that way, at least you know which lock you are applying the timeout to, and have some chance of being able to estimate an appropriate timeout. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
AW: AW: [HACKERS] timeout on lock feature
It is not something that makes anything unrelyable or less robust. How can you argue that? The presence of a lock timeout *will* make operations fail that otherwise would have succeeded; moreover that failure will be pretty unpredictable (at least from the point of view of the application that issued the command). That qualifies as "unreliable and not robust" in my book. A persistent SET variable also opens up the risk of completely unwanted failures in critical operations --- all you have to do is forget to reset the variable ?? So what, when you e.g. forget to commit you are also in trouble, I do not see anything special here. when the effect is no longer wanted. (Murphy's Law guarantees that you won't find out such a mistake until the worst possible time. That's even less robust.) My OLTP clients set it to 30 sec right after connect and leave it at that. The only way PG could apply reasonable timeouts would be for the application to dictate them, That is exactly what we are talking about here. The *real* problem is that the application cannot determine reasonable timeouts either. Perhaps the app can decide how long it is willing to wait overall, Yes, that is it. As I tried to explain earlier, the amount of work that needs to be done for the own tx (in OLTP) is pretty well predictable, but the work of other clients is not. but how can it translate that into the low-level notion of an appropriate lock timeout? It does not know how many locks might get locked in the course of a query, nor which locks they are exactly, nor what the likely distribution of wait intervals is for those locks. The above would imho be a wrong approach at determining the timeout. Given that, using a lock timeout "feature" is just a crapshoot. If you say "set lock timeout X", you have no real idea how that translates to application-visible performance nor how big a risk you are taking of inducing unwanted failures. You don't even get to average out the uncertainty across a whole query, because if any one of the lock waits exceeds X, your query blows up. Yet making X large destroys the usefulness of the feature entirely, so there will always be a strong temptation to set it too low. This is the real reason why I've been holding out for restricting the feature to a specific LOCK TABLE statement: if it's designed that way, at least you know which lock you are applying the timeout to, and have some chance of being able to estimate an appropriate timeout. I do not agree, but such is life :-) BTW: for distributed txns you need a lock timeout feature anyway, because detecting remote deadlocks between two or more different servers would be very complicated. And I do think PostgreSQL will need remote db access a la long. Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: AW: [HACKERS] timeout on lock feature
This is the real reason why I've been holding out for restricting the feature to a specific LOCK TABLE statement: if it's designed that way, at least you know which lock you are applying the timeout to, and have some chance of being able to estimate an appropriate timeout. As I pointed before - it's half useless. And I totally do not understand why to object feature 1. that affects users *only when explicitly requested*; 2. whose implementation costs nothing - ie has no drawbacks for overall system. It was general practice in project so far: if user want some feature and it doesn't affect others - let's do it. What's changed? Vadim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: AW: [HACKERS] timeout on lock feature
One idea Tom had was to make it only active in a transaction, so you do: BEGIN WORK; SET TIMEOUT TO 10; UPDATE tab SET col = 3; COMMIT Tom is concerned people will do the SET and forget to RESET it, causing all queries to be affected by the timeout. And what? Queries would be just aborted. It's not critical event to take responsibility from user. Vadim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: AW: [HACKERS] timeout on lock feature
[ Charset ISO-8859-1 unsupported, converting... ] This is the real reason why I've been holding out for restricting the feature to a specific LOCK TABLE statement: if it's designed that way, at least you know which lock you are applying the timeout to, and have some chance of being able to estimate an appropriate timeout. As I pointed before - it's half useless. And I totally do not understand why to object feature 1. that affects users *only when explicitly requested*; 2. whose implementation costs nothing - ie has no drawbacks for overall system. It was general practice in project so far: if user want some feature and it doesn't affect others - let's do it. What's changed? This is another reason to make it be SET TIMEOUT ... because then we don't have to have this NOWAIT tacked on to every command. It keeps the parser and manual pages cleaner, and it is a non-standard extension. One idea Tom had was to make it only active in a transaction, so you do: BEGIN WORK; SET TIMEOUT TO 10; UPDATE tab SET col = 3; COMMIT Tom is concerned people will do the SET and forget to RESET it, causing all queries to be affected by the timeout. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: AW: [HACKERS] timeout on lock featurey
[ Charset ISO-8859-1 unsupported, converting... ] One idea Tom had was to make it only active in a transaction, so you do: BEGIN WORK; SET TIMEOUT TO 10; UPDATE tab SET col = 3; COMMIT Tom is concerned people will do the SET and forget to RESET it, causing all queries to be affected by the timeout. And what? Queries would be just aborted. It's not critical event to take responsibility from user. Hey, I agree. If the users wants the TIMEOUT, give it to them. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] timeout on lock feature
On Wed, Apr 18, 2001 at 09:54:11AM +0200, Zeugswetter Andreas SB wrote: In short, I think lock timeout is a solution searching in vain for a problem. If we implement it, we are just encouraging bad application design. I agree with Tom completely here. In any real-world application the database is the key component of a larger system: the work it does is the most finicky, and any mistakes (either internally or, more commonly, from misuse) have the most far-reaching consequences. The responsibility of the database is to provide a reliable and easily described and understood mechanism to build on. It is not something that makes anything unrelyable or less robust. It is also simple: "I (the client) request that you (the backend) dont wait for any lock longer than x seconds" Many things that are easy to say have complicated consequences. Timeouts are a system-level mechanism that to be useful must refer to system-level events that are far above anything that PG knows about. I think you are talking about different kinds of timeouts here. Exactly. I'm talking about useful, meaningful timeouts, not random timeouts attached to invisible events within the database. The only way PG could apply reasonable timeouts would be for the application to dictate them, That is exactly what we are talking about here. No. You wrote elsewhere that the application sets "30 seconds" and leaves it. But that 30 seconds doesn't have any application-level meaning -- an operation could take twelve hours without tripping your 30-second timeout. For the application to dictate the timeouts reasonably, PG would have to expose all its lock events to the client and expect it to deduce how they affect overall behavior. but the application can better implement them itself. It can, but it makes the program more complicated (needs timers or threads, which violates your last statement "simplest interface". It is good for the program to be more complicated if it is doing a more complicated thing -- if it means the database may remain simple. People building complex systems have an even greater need for simple components than people building little ones. What might be a reasonable alternative would be a BEGIN timeout: report failure as soon as possible after N seconds unless the timer is reset, such as by a commit. Such a timeout would be meaningful at the database-interface level. It could serve as a useful building block for application-level timeouts when the client environment has trouble applying timeouts on its own. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout on lock featurey
What might be a reasonable alternative would be a BEGIN timeout: report failure as soon as possible after N seconds unless the timer is reset, such as by a commit. Such a timeout would be meaningful at the database-interface level. It could serve as a useful building block for application-level timeouts when the client environment has trouble applying timeouts on its own. Now that is a nifty idea. Just put it on one command, BEGIN, and have it apply for the whole transaction. We could just set an alarm and do a longjump out on timeout. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout on lock feature
On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote: What might be a reasonable alternative would be a BEGIN timeout: report failure as soon as possible after N seconds unless the timer is reset, such as by a commit. Such a timeout would be meaningful at the database-interface level. It could serve as a useful building block for application-level timeouts when the client environment has trouble applying timeouts on its own. Now that is a nifty idea. Just put it on one command, BEGIN, and have it apply for the whole transaction. We could just set an alarm and do a longjump out on timeout. Of course, it begs the question why the client couldn't do that itself, and leave PG out of the picture. But that's what we've been talking about all along. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: [HACKERS] timeout on lock feature
I was thinking SET because UPDATE does an auto-lock. Optimal would imho be a SET that gives a maximum amount of time in seconds the client is willing to wait for any lock. But I liked the efficiency of Henryk's code. Bruce Momjian [EMAIL PROTECTED] writes: I can imagine some people wanting this. However, 7.1 has new deadlock detection code, so I would you make a 7.1 version and send it over. We can get it into 7.2. I object strongly to any such "feature" in the low-level form that Henryk proposes, because it would affect *ALL* locking. Do you really want all your other transactions to go belly-up if, say, someone vacuums pg_class? Yes, if a non batch client already blocked for over x seconds. Of course a more sophisticated client can send querycancel() but that involves a more complicated program (threads, timer ...). A variant of LOCK TABLE that explicitly requests a timeout might make sense, though. I do not think that a solution for one particular lock is very helpful. If your dml then blocks on some unforseen lock (parse, plan ...) , the client is in exactly the situation it tried to avoid in the first place. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: AW: [HACKERS] timeout on lock feature
Added to TODO: * Add SET parameter to timeout if waiting for lock too long I was thinking SET because UPDATE does an auto-lock. Optimal would imho be a SET that gives a maximum amount of time in seconds the client is willing to wait for any lock. But I liked the efficiency of Henryk's code. Bruce Momjian [EMAIL PROTECTED] writes: I can imagine some people wanting this. However, 7.1 has new deadlock detection code, so I would you make a 7.1 version and send it over. We can get it into 7.2. I object strongly to any such "feature" in the low-level form that Henryk proposes, because it would affect *ALL* locking. Do you really want all your other transactions to go belly-up if, say, someone vacuums pg_class? Yes, if a non batch client already blocked for over x seconds. Of course a more sophisticated client can send querycancel() but that involves a more complicated program (threads, timer ...). A variant of LOCK TABLE that explicitly requests a timeout might make sense, though. I do not think that a solution for one particular lock is very helpful. If your dml then blocks on some unforseen lock (parse, plan ...) , the client is in exactly the situation it tried to avoid in the first place. Andreas -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: AW: [HACKERS] timeout on lock feature
I envisioned: SET TIMEOUT TO 10; UPDATE tab SET col = 3; RESET TIMEOUT Can't we get that work work properly? Let the timeout only apply to the 'tab' table and none of the others. As Henryk has implemented it, it WON'T only apply to the 'tab' table; it'll affect all locks grabbed anywhere, including those that the system locks internally. That scares the heck out of me, Andreas' objections notwithstanding. What exactly scares you ? Surely the deadlock resolution should handle the above decision to ABORT in the same way it currently does. If not we have something to fix, no? Of course this might rather be something to consider for 7.2 and not 7.1.1. Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: AW: [HACKERS] timeout on lock feature
Bruce Momjian [EMAIL PROTECTED] writes: Added to TODO: * Add SET parameter to timeout if waiting for lock too long I repeat my strong objection to any global (ie, affecting all locks) timeout. Such a "feature" will have unpleasant consequences. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: AW: [HACKERS] timeout on lock feature
Bruce Momjian [EMAIL PROTECTED] writes: I envisioned: SET TIMEOUT TO 10; UPDATE tab SET col = 3; RESET TIMEOUT Can't we get that work work properly? Let the timeout only apply to the 'tab' table and none of the others. As Henryk has implemented it, it WON'T only apply to the 'tab' table; it'll affect all locks grabbed anywhere, including those that the system locks internally. That scares the heck out of me, Andreas' objections notwithstanding. Can't we exclude system tables from being affected by the timeout? How will you do that? The lock manager makes a point of not knowing the semantics associated with any particular lock tag. It's even less likely to know the difference between a "system" grab and a "user" grab on what might be the very same lock (consider an "UPDATE pg_class" command). Requiring a LOCK statement that matches the UPDATE/DELETE and wrapping the whole thing in a transaction seems needlessly complex to me. As opposed to your three-step proposal above? That doesn't look very much simpler to me... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: AW: [HACKERS] timeout on lock feature
Bruce Momjian [EMAIL PROTECTED] writes: Added to TODO: * Add SET parameter to timeout if waiting for lock too long I repeat my strong objection to any global (ie, affecting all locks) timeout. Such a "feature" will have unpleasant consequences. I envisioned: SET TIMEOUT TO 10; UPDATE tab SET col = 3; RESET TIMEOUT Can't we get that work work properly? Let the timeout only apply to the 'tab' table and none of the others. Can't we exclude system tables from being affected by the timeout? Why exactly would you be willing to wait longer for an implicit system table lock? If this was the case you should also be willing to wait for the row lock, no ? The timeout will be useful to let the client or user decide on an alternate course of action other that killing his application (without the need for timers or threads in the client program). Requiring a LOCK statement that matches the UPDATE/DELETE and wrapping the whole thing in a transaction seems needlessly complex to me. Agreed. Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: AW: [HACKERS] timeout on lock feature
Added to TODO: * Add SET parameter to timeout if waiting for lock too long I repeat my strong objection to any global (ie, affecting all locks) timeout. Such a "feature" will have unpleasant consequences. Except that other people like myself, see those consequences as a pleasant thing :-) And we are talking about something that has to be requested by the client explicitly (at least in a default installation). It simply does make sense for an interactive client to not block more than ~ 30 seconds. Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: AW: [HACKERS] timeout on lock feature
Zeugswetter Andreas SB [EMAIL PROTECTED] writes: The timeout will be useful to let the client or user decide on an alternate course of action other that killing his application (without the need for timers or threads in the client program). Okay, let's take a close look at this assumption. 1. Why is 10 seconds (or 1, or 30) a magic number? If you've waited that long, why wouldn't you be willing to wait a little longer? How will you know what value to pick? 2. If you do want a timeout to support an interactive application, seems to me that you want to specify it as a total time for the whole query, not the maximum delay to acquire any individual lock. Under normal circumstances lock delays are likely to be just a small part of total query time. 3. Since we already have deadlock detection, there is no need for timeouts as a defense against deadlock. A timeout would only be useful to defend against other client applications that are sitting idle or executing long-running operations while holding locks that conflict with your real-time query. This scenario strikes me as a flaw in the overall application design, which should be fixed by fixing those other clients and/or the lock usage. A lock timeout is just a bandaid to cope (poorly) with broken application design. 4. The correct way to deal with overly-long queries in an interactive application is to let the user interactively cancel queries (which we already support). This is much better than any application-specified fixed timeout, because the application is unlikely to be aware of extenuating circumstances --- say, the system is heavily overloaded at the moment because of lots of activity. I can think of few things more annoying than an application-set timeout that kills my unfinished query whenever the system is under load. In short, I think lock timeout is a solution searching in vain for a problem. If we implement it, we are just encouraging bad application design. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: AW: AW: [HACKERS] timeout on lock feature
Zeugswetter Andreas SB [EMAIL PROTECTED] writes: The timeout will be useful to let the client or user decide on an alternate course of action other that killing his application (without the need for timers or threads in the client program). This assumes (without evidence) that the client has a good idea of what the timeout limit ought to be. I think this "feature" has no real use other than encouraging application programmers to shoot themselves in the foot. I see no reason that we should make it easy to misdesign applications. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
AW: AW: AW: [HACKERS] timeout on lock feature
The timeout will be useful to let the client or user decide on an alternate course of action other that killing his application (without the need for timers or threads in the client program). This assumes (without evidence) that the client has a good idea of what the timeout limit ought to be. Yes, the application programmer would need to know how long his users are willing to wait before they start to "hammer at their monitors, kick their pc's or throw the mouse across the room :-O". It must be made clear that it is very counter productive to use too short timeouts. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: AW: [HACKERS] timeout on lock feature
Title: RE: AW: [HACKERS] timeout on lock feature Sorry for my forgetfulness (and a search through geocrawler didn't turn up anything useful), but what was the problem with something like NOWAIT? e.g.: SELECT * FROM a FOR UPDATE NOWAIT; where, if the required lock could not be obtained immediately, this statement would raise an error. Cheers... MikeA -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 17 April 2001 15:49 To: Bruce Momjian Cc: Zeugswetter Andreas SB; Henryk Szal; [EMAIL PROTECTED] Subject: Re: AW: [HACKERS] timeout on lock feature Bruce Momjian [EMAIL PROTECTED] writes: Added to TODO: * Add SET parameter to timeout if waiting for lock too long I repeat my strong objection to any global (ie, affecting all locks) timeout. Such a feature will have unpleasant consequences. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
Re: AW: [HACKERS] timeout on lock feature
Michael Ansley [EMAIL PROTECTED] writes: Sorry for my forgetfulness (and a search through geocrawler didn't turn up anything useful), but what was the problem with something like NOWAIT? e.g.: SELECT * FROM a FOR UPDATE NOWAIT; where, if the required lock could not be obtained immediately, this statement would raise an error. I have no objection to that ... it does not cover anything except FOR UPDATE, though, which is probably less general than some of the other folks want. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
RE: AW: [HACKERS] timeout on lock feature
Added to TODO: * Add SET parameter to timeout if waiting for lock too long I repeat my strong objection to any global (ie, affecting all locks) timeout. Such a "feature" will have unpleasant consequences. But LOCK TABLE T IN ROW EXCLUSIVE MODE WITH TIMEOUT X will not give required results not only due to parser/planner locks - what if UPDATE T will have to wait for other transactions commit/abort (same row update)? Lock on pseudo-table is acquired in this case... Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: AW: AW: [HACKERS] timeout on lock feature
The timeout will be useful to let the client or user decide on an alternate course of action other that killing his application (without the need for timers or threads in the client program). This assumes (without evidence) that the client has a good idea of what the timeout limit ought to be. I think this "feature" has no real use other than encouraging application programmers to shoot themselves in the foot. I see no reason that we should make it easy to misdesign applications. AFAIR, Big Boys have this feature. If its implementation is safe, ie will not affect applications not using it, why do not implement it? Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] timeout on lock feature
On Tue, Apr 17, 2001 at 12:56:11PM -0400, Tom Lane wrote: In short, I think lock timeout is a solution searching in vain for a problem. If we implement it, we are just encouraging bad application design. I agree with Tom completely here. In any real-world application the database is the key component of a larger system: the work it does is the most finicky, and any mistakes (either internally or, more commonly, from misuse) have the most far-reaching consequences. The responsibility of the database is to provide a reliable and easily described and understood mechanism to build on. Timeouts are a system-level mechanism that to be useful must refer to system-level events that are far above anything that PG knows about. The only way PG could apply reasonable timeouts would be for the application to dictate them, but the application can better implement them itself. You can think of this as another aspect of the "end-to-end" principle: any system-level construct duplicated in a lower-level system component can only improve efficiency, not provide the corresponding high-level service. If we have timeouts in the database, they should be there to enable the database to better implement its abstraction, and not pretend to be a substitute for system-level timeouts. There's no upper limit on how complicated a database interface can become (cf. Oracle). The database serves its users best by having the simplest interface that can possibly provide the needed service. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout on lock feature
Timeouts are a system-level mechanism that to be useful must refer to system-level events that are far above anything that PG knows about. The only way PG could apply reasonable timeouts would be for the application to dictate them, but the application can better implement them itself. OK we have the following scenario Session A Session B begin begin insert -- on unique constraint insert -- on same unique constraint -- Session A becomes idle : -- Session B becomes ... or we have (Informix Online) Session A Session B set lock mode to wait [seconds] set lock mode to wait [seconds] begin begin insert -- on unique constraint insert -- on same unique constraint * resource not available error * -- Session B carries on Oracle 7 (OCI) has oopt() call to set wait options for requested resources. Oracle 8 OCI has the same behaviour as PG ie. oopt() is no longer available. I believe that the ability to switch the database to either not wait for resources, or wait a specified period or wait forever (default) is essential especially for interactive applications. Regards Theo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout on lock feature
Bruce Momjian [EMAIL PROTECTED] writes: I can imagine some people wanting this. However, 7.1 has new deadlock detection code, so I would you make a 7.1 version and send it over. We can get it into 7.2. I object strongly to any such "feature" in the low-level form that Henryk proposes, because it would affect *ALL* locking. Do you really want all your other transactions to go belly-up if, say, someone vacuums pg_class? A variant of LOCK TABLE that explicitly requests a timeout might make sense, though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] timeout on lock feature
I was thinking SET because UPDATE does an auto-lock. Bruce Momjian [EMAIL PROTECTED] writes: I can imagine some people wanting this. However, 7.1 has new deadlock detection code, so I would you make a 7.1 version and send it over. We can get it into 7.2. I object strongly to any such "feature" in the low-level form that Henryk proposes, because it would affect *ALL* locking. Do you really want all your other transactions to go belly-up if, say, someone vacuums pg_class? A variant of LOCK TABLE that explicitly requests a timeout might make sense, though. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout on lock feature
Bruce Momjian [EMAIL PROTECTED] writes: I was thinking SET because UPDATE does an auto-lock. Not to mention a ton of implicit locks acquired on various system tables during parsing/planning. You really want auto timeout on all of those? I sure don't. The appropriate way to do this given a LOCK TABLE option would be like BEGIN; LOCK TABLE foo IN ROW EXCLUSIVE MODE WITH TIMEOUT n; UPDATE foo SET ...; COMMIT; which restricts the scope of the timeout behavior to just the specific lock that the user is thinking of, and doesn't risk breaking fundamental system operations. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] timeout on lock
Hi,
i implement additional server functionality. Currently (v7.0.3), executing
SQL update statement on the same
row from inside two different processess results in blocking second process
to the end of transaction in the first one.
In real OLTP application second process can't wait too long. After few
seconds server should return with
message 'lock timeout exceeded'. I modify postgres lock manager source code
to obtain that functionality.
I take advantage of deadlock detection mechanism. Currently deadlock
detection routine initialy check for
simple deadlock detection between two processess, next insert lock into lock
queue and after
DEADLOCK_CHECK_TIMER seconds run HandleDeadLock to comprehensive deadlock
detection.
To obtain 'timeout on lock' feature I do as follow:
1. Add new configure parameter. I add #define statement in file
include/config.in
#define NO_WAIT_FOR_LOCK 1
In the future somebody can add new option to SQL SET command
2. Modify HandleDeadLock routine. In file backend/storage/lmgr/proc.c change
lines 866-870
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
to
if (!NO_WAIT_FOR_LOCK)
{
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
}
With this modyfication every conflicting lock wait DEADLOCK_CHECK_TIMER
seconds in queue and returns with error
'deadlock detect'.
Who can add 'timeout on lock' feature to the next postgres server release?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[HACKERS] timeout on lock feature
Hi,
I implement additional server functionality. Currently (v7.0.3), executing
SQL update statement on the same
row from inside two different processess results in blocking second process
to the end of transaction in
the first one. In real OLTP application second process can't wait too long.
After few seconds server should
return to the application message:'lock timeout exceeded'. I modify postgres
lock manager source code to
obtain that functionality. I take advantage of deadlock detection mechanism.
Currently deadlock
detection routine initialy check for simple deadlock detection between two
processess, next insert lock
into lock queue and after DEADLOCK_CHECK_TIMER seconds run HandleDeadLock to
comprehensive deadlock detection.
To obtain 'timeout on lock' feature I do as follow:
1. Add new configure parameter. Currently I add #define statement in file
include/config.in
#define NO_WAIT_FOR_LOCK 1
In the future somebody can add new option to SQL SET command
2. Modify HandleDeadLock routine. In file backend/storage/lmgr/proc.c change
lines 866-870
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
to
if (!NO_WAIT_FOR_LOCK)
{
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
}
With this modyfication every conflicting lock wait DEADLOCK_CHECK_TIMER
seconds in queue and returns with error
'deadlock detect'.
Who can add this simply 'timeout on lock' implementation to the next
postgres server release?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout on lock feature
If you can't handle the SET variable stuff, we can do it over here.
Thanks.
Hi,
I implement additional server functionality. Currently (v7.0.3), executing
SQL update statement on the same
row from inside two different processess results in blocking second process
to the end of transaction in
the first one. In real OLTP application second process can't wait too long.
After few seconds server should
return to the application message:'lock timeout exceeded'. I modify postgres
lock manager source code to
obtain that functionality. I take advantage of deadlock detection mechanism.
Currently deadlock
detection routine initialy check for simple deadlock detection between two
processess, next insert lock
into lock queue and after DEADLOCK_CHECK_TIMER seconds run HandleDeadLock to
comprehensive deadlock detection.
To obtain 'timeout on lock' feature I do as follow:
1. Add new configure parameter. Currently I add #define statement in file
include/config.in
#define NO_WAIT_FOR_LOCK 1
In the future somebody can add new option to SQL SET command
2. Modify HandleDeadLock routine. In file backend/storage/lmgr/proc.c change
lines 866-870
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
to
if (!NO_WAIT_FOR_LOCK)
{
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
}
With this modyfication every conflicting lock wait DEADLOCK_CHECK_TIMER
seconds in queue and returns with error
'deadlock detect'.
Who can add this simply 'timeout on lock' implementation to the next
postgres server release?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout on lock feature
I can imagine some people wanting this. However, 7.1 has new deadlock
detection code, so I would you make a 7.1 version and send it over. We
can get it into 7.2. I think we need a SET variable, and it should
default to OFF.
Good idea. Thanks.
Hi,
I implement additional server functionality. Currently (v7.0.3), executing
SQL update statement on the same
row from inside two different processess results in blocking second process
to the end of transaction in
the first one. In real OLTP application second process can't wait too long.
After few seconds server should
return to the application message:'lock timeout exceeded'. I modify postgres
lock manager source code to
obtain that functionality. I take advantage of deadlock detection mechanism.
Currently deadlock
detection routine initialy check for simple deadlock detection between two
processess, next insert lock
into lock queue and after DEADLOCK_CHECK_TIMER seconds run HandleDeadLock to
comprehensive deadlock detection.
To obtain 'timeout on lock' feature I do as follow:
1. Add new configure parameter. Currently I add #define statement in file
include/config.in
#define NO_WAIT_FOR_LOCK 1
In the future somebody can add new option to SQL SET command
2. Modify HandleDeadLock routine. In file backend/storage/lmgr/proc.c change
lines 866-870
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
to
if (!NO_WAIT_FOR_LOCK)
{
if (!DeadLockCheck(MyProc, MyProc-waitLock))
{
UnlockLockTable();
return;
}
}
With this modyfication every conflicting lock wait DEADLOCK_CHECK_TIMER
seconds in queue and returns with error
'deadlock detect'.
Who can add this simply 'timeout on lock' implementation to the next
postgres server release?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
