Re: [HACKERS] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Rod Taylor wrote: Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. LOCK NOWAIT is only helpful if you can express your problem as not wanting to wait for a table-level lock. When you're trying to grab a row-level lock via SELECT FOR UPDATE, there isn't any provision for NOWAIT. The notion of a global lock_timeout setting is bogus IMHO, because every proposed application of it has failed to consider the locks taken internally by the system. But that objection wouldn't apply to a SELECT FOR UPDATE NOWAIT command where the no wait behavior only applied to the row lock being explicitly grabbed. I thought I remembered someone working on such a thing just recently. Added to TODO: * Allow FOR UPDATE queries to do NOWAIT locks -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
ronzo wrote: Hi Was already implemented the timeout on the SELECT ... FOR UPDATE (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT? Please note: ours need is the timeout/verify at the ROW level, not at the table level. Is already OK? Is in the TODO list? May you suggest an alternative method? We have discussed this at length and no one could state why having an timeout per lock is any better than using a statement_timeout. We can not do a NOWAIT on a single SELECT because there are alot of locks used even for a select and having them fail randomly would be useless. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote: We have discussed this at length and no one could state why having an timeout per lock is any better than using a statement_timeout. Actually, I hit one. I have a simple queue and a number of processes pulling jobs out of the queue. Due to transactional requirements, the database is appropriate for a first cut. Anyway, a statement_timeout of 100ms is usually plenty to determine that the job is being processed, and for one of the pollers to move on, but every once in a while a large job (4 to 5MB chunk of data) would find itself in the queue which takes more than 100ms to pull out. Not a big deal, just bump the timeout in this case. Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... -- ---(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] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Rod Taylor wrote: On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote: We have discussed this at length and no one could state why having an timeout per lock is any better than using a statement_timeout. Actually, I hit one. I have a simple queue and a number of processes pulling jobs out of the queue. Due to transactional requirements, the database is appropriate for a first cut. Anyway, a statement_timeout of 100ms is usually plenty to determine that the job is being processed, and for one of the pollers to move on, but every once in a while a large job (4 to 5MB chunk of data) would find itself in the queue which takes more than 100ms to pull out. Not a big deal, just bump the timeout in this case. Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Bruce Momjian [EMAIL PROTECTED] writes: Rod Taylor wrote: Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. LOCK NOWAIT is only helpful if you can express your problem as not wanting to wait for a table-level lock. When you're trying to grab a row-level lock via SELECT FOR UPDATE, there isn't any provision for NOWAIT. The notion of a global lock_timeout setting is bogus IMHO, because every proposed application of it has failed to consider the locks taken internally by the system. But that objection wouldn't apply to a SELECT FOR UPDATE NOWAIT command where the no wait behavior only applied to the row lock being explicitly grabbed. I thought I remembered someone working on such a thing just recently. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
On Wed, 2004-11-24 at 22:47 -0500, Bruce Momjian wrote: Rod Taylor wrote: On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote: We have discussed this at length and no one could state why having an timeout per lock is any better than using a statement_timeout. Actually, I hit one. I have a simple queue and a number of processes pulling jobs out of the queue. Due to transactional requirements, the database is appropriate for a first cut. Anyway, a statement_timeout of 100ms is usually plenty to determine that the job is being processed, and for one of the pollers to move on, but every once in a while a large job (4 to 5MB chunk of data) would find itself in the queue which takes more than 100ms to pull out. Not a big deal, just bump the timeout in this case. Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. On a for update? -- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
There is a statement_timeout that will control how long a statement can
execute before being cancelled. We have never agreed that controlling
how long we wait for an individual lock is valuable.
---
Robert Treat wrote:
On Thursday 21 October 2004 06:44, you wrote:
Hi
Was already implemented the timeout on the SELECT ... FOR UPDATE
(non-blocking lock) and/or is possible known if the lock exist on the
specified ROW before executing the SELECT?
No.
Please note: ours need is the timeout/verify at the ROW level, not at the
table level.
Is already OK? Is in the TODO list?
May you suggest an alternative method?
Thank you.
You would need a more extensive implementation of row level locks than
PostgreSQL currently offers. There have been discussions about this in the
past, but afaik no one is actively working on it. You can probably find more
info in the archives about it, also I believe it is on the TODO list, so you
might find some more detail by looking there.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(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] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
On Thursday 21 October 2004 06:44, you wrote:
Hi
Was already implemented the timeout on the SELECT ... FOR UPDATE
(non-blocking lock) and/or is possible known if the lock exist on the
specified ROW before executing the SELECT?
No.
Please note: ours need is the timeout/verify at the ROW level, not at the
table level.
Is already OK? Is in the TODO list?
May you suggest an alternative method?
Thank you.
You would need a more extensive implementation of row level locks than
PostgreSQL currently offers. There have been discussions about this in the
past, but afaik no one is actively working on it. You can probably find more
info in the archives about it, also I believe it is on the TODO list, so you
might find some more detail by looking there.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[HACKERS] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Hi Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT? Please note: ours need is the timeout/verify at the ROW level, not at the table level. Is already OK? Is in the TODO list? May you suggest an alternative method? Thank you.
