AW: AW: timeout on lock feature - Mailing list pgsql-hackers

> > 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

?????? So what, when you e.g. forget to commit you are also in trouble,
I do not see anything special here.

> 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.)

My OLTP clients set it to 30 sec right after connect and leave it at that.  

> 
> >> 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,

Yes, that is it. As I tried to explain earlier, the amount of work that needs to be 
done for the own tx (in OLTP) is pretty well predictable, but the work of other 
clients is not.

> 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.

The above would imho be a wrong approach at determining the timeout.

> 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.

I do not agree, but such is life :-)

BTW: for distributed txns you need a lock timeout feature anyway, because 
detecting remote deadlocks between two or more different servers would be 
very complicated. And I do think PostgreSQL will need remote db access a la long.

Andreas