Re: [HACKERS] lock timeout patch
On Tue, Jun 29, 2004 at 09:25:27 +0900, Satoshi Nagayasu [EMAIL PROTECTED] wrote: But I don't want to wait one or more minutes just for a lock. I need to return a message to the user retry later. or something like that. It depends on various applications. Why not set statement timeout low when you are about to run a query that you think should return quickly? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] lock timeout patch
Tom, I'd accept a mechanism to enforce a timeout at the lock level if you could show me a convincing use-case for lock timeouts instead of statement timeouts, but I don't believe there is one. I think this proposal is a solution in search of a problem. Hmmm ... didn't we argue this out with NOWAIT? What did we conclude then? I'm reluctant to go over old ground repeatedly. Let me say for myself that I would use this feature if it existed, but would not miss it a whole lot if the patch was rejected.Here's the idea: I have an OLAP database of regional office evaluations (in SQL Server, sadly) which requires that the evaluations, sometimes interlocking, of regions be closed simultaneously (in one transaction). This means that during the closure process, certain kinds of data entry needs to be frozen out. I am using SQL Server's lock timeout functionality for this; bascially, the data entry waits for 30 seconds, and then tells the user to try again in 10 minutes. I could do the same thing in PostgreSQL using NOWAIT and a loop on the client side. But the lock timeout is somewhat easier. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] lock timeout patch
On Tue, 2004-06-29 at 18:36, Josh Berkus wrote: Tom, I'd accept a mechanism to enforce a timeout at the lock level if you could show me a convincing use-case for lock timeouts instead of statement timeouts, but I don't believe there is one. I think this proposal is a solution in search of a problem. Hmmm ... didn't we argue this out with NOWAIT? What did we conclude then? I'm reluctant to go over old ground repeatedly. Let me say for myself that I would use this feature if it existed, but would not miss it a whole lot if the patch was rejected.Here's the idea: Can't vouch for the patch, but I can say this would get used... I have an ... database ... which requires that the evaluations, sometimes interlocking, of regions be closed simultaneously (in one transaction). This means that during the closure process, certain kinds of data entry needs to be frozen out. I am using ... lock timeout functionality for this; bascially, the data entry waits for 30 seconds, and then tells the user to try again in 10 minutes. Just implementing this same scenario, using DB2 (...). Of course, if I had MVCC on that application, I could argue that this is not required...is that the basis of the not required view? I could do the same thing in PostgreSQL using NOWAIT and a loop on the client side. But the lock timeout is somewhat easier. SQLServer and DB2 support a lock timeout system wide, simple but not granular. Oracle supports the NOWAIT option, even though it supports readers-dont-block locking. I prefer the NOWAIT option as it gives a more detailed handle on the exact statements that you wish to wait, or not. Without NOWAIT, we would need to set lock_timeout = 30 (seconds) Statement level timeout is a different thing entirely, since there are very often statements that need to run for 2-3 hours (even more in some cases), so statement level timeout is set to 1 (seconds). Best Regards, Simon Riggs ---(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: [HACKERS] lock timeout patch
Tom, I'd accept a mechanism to enforce a timeout at the lock level if you could show me a convincing use-case for lock timeouts instead of statement timeouts, but I don't believe there is one. I think this proposal is a solution in search of a problem. Hmmm ... didn't we argue this out with NOWAIT? What did we conclude then? I'm reluctant to go over old ground repeatedly. The result of this debate was that there was some use for it. NOWAIT is now implemented for table locking but not for row locking. Personally I think there is some use for forcing transactions to abort as soon as a lock situation is detected (although I probably wouldn't use it). For row level locking I would suggest to the original poster to compare xmin/xmax (check the docs) to pre check the row level lock condition. This is inelegant but it mostly works. FWIW, I think the treatment of locking in the docs could use some improvement. Especially wrt MVCC and pessimistic locking and the 'big picture' issues going on there (especially why the former is better than the latter). Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] lock timeout patch
Merlin Moncure [EMAIL PROTECTED] writes: FWIW, I think the treatment of locking in the docs could use some improvement. Especially wrt MVCC and pessimistic locking and the 'big picture' issues going on there (especially why the former is better than the latter). Send a patch ... 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: [HACKERS] lock timeout patch
Tom Lane wrote: I'd accept a mechanism to enforce a timeout at the lock level if you could show me a convincing use-case for lock timeouts instead of statement timeouts, but I don't believe there is one. I think this proposal is a solution in search of a problem. I think statement_timeout and lock_timeout are different. If I set statement_timeout to 1000 to detect a lock timeout, I can't run a query which takes over 1 sec. If a lock wait is occured, I want to detect it immediately, but I still want to run a long-running query. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] lock timeout patch
On Mon, 28 Jun 2004, Satoshi Nagayasu wrote: If I set statement_timeout to 1000 to detect a lock timeout, I can't run a query which takes over 1 sec. If a lock wait is occured, I want to detect it immediately, but I still want to run a long-running query. Why is it important what it is that makes your query not return as fast as you expect? Maybe it's locking, maybe the computer is swapping, maybe it's just lack of IO to that disk that holds the table, maybe it does a big sort and have too little sort_mem to do it fast, ... What makes locking special? -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] lock timeout patch
On Mon, 2004-06-28 at 02:16, Satoshi Nagayasu wrote:
Tom Lane wrote:
I'd accept a mechanism to enforce a timeout at the lock level if you
could show me a convincing use-case for lock timeouts instead of
statement timeouts, but I don't believe there is one. I think this
proposal is a solution in search of a problem.
I think statement_timeout and lock_timeout are different.
If I set statement_timeout to 1000 to detect a lock timeout,
I can't run a query which takes over 1 sec.
If a lock wait is occured, I want to detect it immediately,
but I still want to run a long-running query.
How is your problem not solved by NOWAIT?
http://developer.postgresql.org/docs/postgres/sql-lock.html
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] lock timeout patch
Robert Treat wrote: I think statement_timeout and lock_timeout are different. If I set statement_timeout to 1000 to detect a lock timeout, I can't run a query which takes over 1 sec. If a lock wait is occured, I want to detect it immediately, but I still want to run a long-running query. How is your problem not solved by NOWAIT? http://developer.postgresql.org/docs/postgres/sql-lock.html I agree that it's one of the solutions when we use LOCK explicitly. But LOCK does only lock a whole table, doesn't it? -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] lock timeout patch
Dennis Bjorklund wrote: If I set statement_timeout to 1000 to detect a lock timeout, I can't run a query which takes over 1 sec. If a lock wait is occured, I want to detect it immediately, but I still want to run a long-running query. Why is it important what it is that makes your query not return as fast as you expect? Maybe it's locking, maybe the computer is swapping, maybe it's just lack of IO to that disk that holds the table, maybe it does a big sort and have too little sort_mem to do it fast, ... What makes locking special? Processing slow-down is just a hardware/software sizing issue. Of course we need to fix it when a problem is occured, but I think it's a different kind of problem. In large databases, such as DSS(decision support system), some queries takes one or more minutes. I think it's okey. But I don't want to wait one or more minutes just for a lock. I need to return a message to the user retry later. or something like that. It depends on various applications. So I think detecting a lock waiting is important. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] lock timeout patch
Tom, I guess the transaction cancellation from the client using PQrequestCancel() is available, but the cancellation logic must be implemented in the client-application using signal or thread. I think detecting such situation on server-side is not available now, and SQL Server or DB2 have same function. Tom Lane wrote: Satoshi Nagayasu [EMAIL PROTECTED] writes: When a transaction is blocked by another transaction because of waiting a lock, we need a lock timeout in some cases. Isn't there an existing solution for this problem? regards, tom lane -- NAGAYASU Satoshi [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] lock timeout patch
Satoshi Nagayasu [EMAIL PROTECTED] writes: I guess the transaction cancellation from the client using PQrequestCancel() is available, but the cancellation logic must be implemented in the client-application using signal or thread. Actually I think the recommended solution involves using statement_timeout. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] lock timeout patch
statement_timeout terminates large sort or scan even if it is running, doesn't it? statement_timeout doesn't care that the process is waiting a lock or running. I don't want to terminate a running query. So a lock waiting backend shold be killed. Tom Lane wrote: Satoshi Nagayasu [EMAIL PROTECTED] writes: I guess the transaction cancellation from the client using PQrequestCancel() is available, but the cancellation logic must be implemented in the client-application using signal or thread. Actually I think the recommended solution involves using statement_timeout. regards, tom lane -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] lock timeout patch
Satoshi Nagayasu [EMAIL PROTECTED] writes: statement_timeout terminates large sort or scan even if it is running, doesn't it? statement_timeout doesn't care that the process is waiting a lock or running. I don't want to terminate a running query. So a lock waiting backend shold be killed. This argument holds no water. On what will you base your estimate of a good value for lock_timeout? It is nothing more than your estimate of the statement runtime for some other backend that is currently holding the lock you want ... an estimate which surely has less, not more, reliability than the estimate you could make of the maximum runtime of your own statement, because you have less information about just what that other backend is doing. (And both you and the other backend are in turn dependent on waiting for locks held by third parties, etc etc.) I'd accept a mechanism to enforce a timeout at the lock level if you could show me a convincing use-case for lock timeouts instead of statement timeouts, but I don't believe there is one. I think this proposal is a solution in search of a problem. 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])
