Re: AW: timeout on lock feature

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> 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