Re: Feature freeze date for 8.1 - Mailing list pgsql-hackers

Neil Conway <neilc@samurai.com> writes:
> Tom Lane wrote:
>> We would?  Why?  Please provide a motivation that justifies the
>> considerably higher cost to make it count that way, as opposed to
>> time-since-BEGIN.

> The specific scenario this feature is intended to resolve is 
> idle-in-transaction backends holding on to resources while the network 
> connection times out; it isn't intended to implement "I never want to 
> run a transaction that takes more than X seconds to execute."

[ itch... ]  This seems to me to be conflating several distinct issues.
AFAIR the points that have been raised in the thread are:

#1  Defend against loss of connectivity to client
#2  Defend against client sitting idle while holding locks (or just   holding an open transaction and thereby
preventingVACUUM cleanup)
 
#3  Defend against client holding locks unreasonably long, even though   not idle (obviously any such constraint will
causeclients to   fail midstream, but perhaps some DBAs will see this as the lesser   of two evils)
 

I claim that if you have a problem with #1 you ought to go discuss it
with some TCP hackers: you basically want to second-guess the TCP
stack's ideas about appropriate timeouts.  Maybe you know what you
are doing or maybe not, but it's not a database-level issue.

#2 is a fair point if you need to cope with poorly-programmed clients,
but I'm not seeing exactly why it has to be measured by "idle time"
rather than "total time".  The known cases of this involve client
code that issues a BEGIN and then just sits, so there's no difference.

For point #3, I claim you have to measure total time not idle time
or you'll fail to perceive the problem at all.

> While long-running transactions aren't usually a great idea, I can
> certainly imagine installations in which some transactions might take,
> say, 30 minutes to execute but the admin would like to timeout idle
> connections in less than that amount of time.

The fallacy in that argument is that if you don't like a transaction
that sits idle for 30 minutes, you aren't likely to like ones that hog
the CPU for 30 minutes either.  The idle xact is certainly not chewing
more resources than the busy xact.  If you have a problem with it, it
has to be along the lines of holding-locks-too-long, and that would
apply just as much to the busy guy.
        regards, tom lane