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