
> > In short, I think lock timeout is a solution searching in vain for a
> > problem.  If we implement it, we are just encouraging bad > application
> > design.
> 
> I agree with Tom completely here.
> 
> In any real-world application the database is the key component of a 
> larger system: the work it does is the most finicky, and any mistakes
> (either internally or, more commonly, from misuse) have the most 
> far-reaching consequences.  The responsibility of the database is to 
> provide a reliable and easily described and understood mechanism to 
> build on.

It is not something that makes anything unrelyable or less robust.
It is also simple: "I (the client) request that you (the backend) dont wait for 
any lock longer than x seconds"

> Timeouts are a system-level mechanism that to be useful must refer to 
> system-level events that are far above anything that PG knows about.

I think you are talking about different kinds of timeouts here.  

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

> but the application can better implement them itself.

It can, but it makes the program more complicated (needs timers or threads, 
which violates your last statement "simplest interface".

> 
> You can think of this as another aspect of the "end-to-end" principle: 
> any system-level construct duplicated in a lower-level system component 
> can only improve efficiency, not provide the corresponding high-level 
> service.  If we have timeouts in the database, they should be there to
> enable the database to better implement its abstraction, and not pretend 
> to be a substitute for system-level timeouts.

Mentioned functionality has nothing to do with above statement which
I can fully support. 

> There's no upper limit on how complicated a database interface can
> become (cf. Oracle).  The database serves its users best by having 
> the simplest interface that can possibly provide the needed service.

Agreed.

Andreas

Wed, 18 Apr 2001 09:54:11 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: timeout on lock feature
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.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

Wed, 18 Apr 2001 10:36:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: timeout on lock feature 
On Wed, Apr 18, 2001 at 09:54:11AM +0200, Zeugswetter Andreas SB wrote:
> > > In short, I think lock timeout is a solution searching in vain for a
> > > problem.  If we implement it, we are just encouraging bad application
> > > design.
> > 
> > I agree with Tom completely here.
> > 
> > In any real-world application the database is the key component of a 
> > larger system: the work it does is the most finicky, and any mistakes
> > (either internally or, more commonly, from misuse) have the most 
> > far-reaching consequences.  The responsibility of the database is to 
> > provide a reliable and easily described and understood mechanism to 
> > build on.
> 
> It is not something that makes anything unrelyable or less robust.
> It is also simple: "I (the client) request that you (the backend) 
> dont wait for any lock longer than x seconds"

Many things that are easy to say have complicated consequences.

> > Timeouts are a system-level mechanism that to be useful must refer to 
> > system-level events that are far above anything that PG knows about.
> 
> I think you are talking about different kinds of timeouts here.  

Exactly.  I'm talking about useful, meaningful timeouts, not random
timeouts attached to invisible events within the database.

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

No.  You wrote elsewhere that the application sets "30 seconds" and
leaves it.  But that 30 seconds doesn't have any application-level
meaning -- an operation could take twelve hours without tripping your
30-second timeout.  For the application to dictate the timeouts
reasonably, PG would have to expose all its lock events to the client
and expect it to deduce how they affect overall behavior.

> > but the application can better implement them itself.
> 
> It can, but it makes the program more complicated (needs timers 
> or threads, which violates your last statement "simplest interface".

It is good for the program to be more complicated if it is doing a 
more complicated thing -- if it means the database may remain simple.  
People building complex systems have an even greater need for simple
components than people building little ones.
  
What might be a reasonable alternative would be a BEGIN timeout: report 
failure as soon as possible after N seconds unless the timer is reset, 
such as by a commit.  Such a timeout would be meaningful at the 
database-interface level.  It could serve as a useful building block 
for application-level timeouts when the client environment has trouble 
applying timeouts on its own.

Nathan Myers
ncm@zembu.com

Wed, 18 Apr 2001 14:35:30 -0700
ncm@zembu.com (Nathan Myers)
Re: timeout on lock feature
> What might be a reasonable alternative would be a BEGIN timeout: report 
> failure as soon as possible after N seconds unless the timer is reset, 
> such as by a commit.  Such a timeout would be meaningful at the 
> database-interface level.  It could serve as a useful building block 
> for application-level timeouts when the client environment has trouble 
> applying timeouts on its own.

Now that is a nifty idea.  Just put it on one command, BEGIN, and have
it apply for the whole transaction.  We could just set an alarm and do a
longjump out on timeout.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 18 Apr 2001 19:33:24 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: timeout on lock featurey
On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote:
> > What might be a reasonable alternative would be a BEGIN timeout: report 
> > failure as soon as possible after N seconds unless the timer is reset, 
> > such as by a commit.  Such a timeout would be meaningful at the 
> > database-interface level.  It could serve as a useful building block 
> > for application-level timeouts when the client environment has trouble 
> > applying timeouts on its own.
> 
> Now that is a nifty idea.  Just put it on one command, BEGIN, and have
> it apply for the whole transaction.  We could just set an alarm and do a
> longjump out on timeout.

Of course, it begs the question why the client couldn't do that
itself, and leave PG out of the picture.  But that's what we've 
been talking about all along.

Nathan Myers
ncm@zembu.com

Wed, 18 Apr 2001 18:09:46 -0700
ncm@zembu.com (Nathan Myers)
Re: timeout on lock feature
> On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote:
> > > What might be a reasonable alternative would be a BEGIN timeout: report 
> > > failure as soon as possible after N seconds unless the timer is reset, 
> > > such as by a commit.  Such a timeout would be meaningful at the 
> > > database-interface level.  It could serve as a useful building block 
> > > for application-level timeouts when the client environment has trouble 
> > > applying timeouts on its own.
> > 
> > Now that is a nifty idea.  Just put it on one command, BEGIN, and have
> > it apply for the whole transaction.  We could just set an alarm and do a
> > longjump out on timeout.
> 
> Of course, it begs the question why the client couldn't do that
> itself, and leave PG out of the picture.  But that's what we've 
> been talking about all along.

Yes, they can, but of course, they could code the database in the
application too.  It is much easier to put the timeout in a psql script
than to try and code it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 18 Apr 2001 21:39:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: timeout on lock feature
On Wed, Apr 18, 2001 at 09:39:39PM -0400, Bruce Momjian wrote:
> > On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote:
> > > > What might be a reasonable alternative would be a BEGIN timeout:
> > > > report failure as soon as possible after N seconds unless the
> > > > timer is reset, such as by a commit. Such a timeout would be
> > > > meaningful at the database-interface level. It could serve as a
> > > > useful building block for application-level timeouts when the
> > > > client environment has trouble applying timeouts on its own.
> > > 
> > > Now that is a nifty idea. Just put it on one command, BEGIN, and
> > > have it apply for the whole transaction. We could just set an
> > > alarm and do a longjump out on timeout.
> > 
> > Of course, it begs the question why the client couldn't do that
> > itself, and leave PG out of the picture.  But that's what we've 
> > been talking about all along.
> 
> Yes, they can, but of course, they could code the database in the
> application too.  It is much easier to put the timeout in a psql script
> than to try and code it.

Good: add a timeout feature to psql.  

There's no limit to what features you might add to the database 
core once you decide that new features need have nothing to do with 
databases.  Why not (drum roll...) deliver e-mail?

Nathan Myers
ncm@zembu.com

Wed, 18 Apr 2001 19:01:01 -0700
ncm@zembu.com (Nathan Myers)
Re: timeout on lock feature