Re: lock - feedback

Thomas Rokohl wrote:
>>>
>>> so but i'm not sure, doesn't it mean that all statements that take
>>> longer than this time will be abort?
>>> if it is so, that isn't a solution for this problem because than i
>>> can't make a "big" request anymore.
>>
>> You can change it during a session "set statement_timeout=2000"
>>
> yes, but that isn't a certain event that say that the data is locked!
> also the network for example can determine different timeouts.

Yes, but network timeouts will be of the order of several minutes -
hours. You're not holding your locks for that long are you? Are you
really saying your SELECT FOR UPDATE queries can take more than a couple
of seconds to complete?

> and also i don't know the working time for each request before.
> that isn't a solution for me. i will open a "please wait, while the data
> is locked" - window, if the data is locked.

So, you want to write code something like:
   WHILE (still_trying)
     SELECT ... FOR UPDATE
     IF (failed) THEN
       IF (errcode=lock) THEN display_please_wait_message()
       ELSE display_error_message_and_abort()
     END IF
     ELSE
       got_lock = true
     END IF
   END WHILE
   IF got_lock THEN
     do_my_updates()
   END IF

All I'm saying is around "SELECT ... FOR UPDATE" you put two "SET
statement_timeout" to set a short timeout and then revert it.

> and it seems as if this should be a problem with postgres :-(

Perhaps - you can apply NOWAIT to "LOCK TABLE" and I think in 8.1 to
"SELECT FOR UPDATE" also. That's not much use if you're stuck with 8.0
for the moment though. It also doesn't actually change the logic of the
code above.

Or am I missing something here?

--
   Richard Huxton
   Archonet Ltd