Thomas Rokohl wrote:
> Richard Huxton wrote:
>> 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?
> yes normally not, but it can happened. let something goes wrong, for
> example the network is busy or the db server is busy or something like
> that. if the number of request to a server is high enough it will be slow.
> whatever it isn't a certain event for locks.
>
>> All I'm saying is around "SELECT ... FOR UPDATE" you put two "SET
>> statement_timeout" to set a short timeout and then revert it.
> yes it is clear what you mean but it can also make trouble and a
> deadlock if the timeout is the short for the request.
No, you can't get a deadlock. You could get an update failing when it
could theoretically have worked if you had a longer timeout.
>>> 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.
> i had read it and i had trouble with the beta so i us the stable version.
> but NOWAIT is a certain event for locks, the timeout isn't one.
Actually, in either case you'll need to check the return-code. Just
because you added NOWAIT doesn't mean there can't be some other reason
the query failed.
As you have pointed out though, if your query needs 3 seconds and you
set the statement timeout to 2 seconds then it'll never work. In
practice, it's less of a problem than you might think.
--
Richard Huxton
Archonet Ltd