Re: Idle session timeout?

Sean Murphy wrote:
> Scott Marlowe wrote:
>> On Tue, 2007-05-08 at 15:59, Sean Murphy wrote:
>>> Scott Marlowe wrote:
>>>> On Tue, 2007-05-08 at 12:19, Sean Murphy wrote:
>>>>> Tom Lane wrote:
>>>>>> Sean Murphy <Sean(dot)Murphy(at)equipoint(dot)com> writes:
>>>>>>> I'm WAY out of my depth here, but my impression, based on the
>>>>>>> circumstances, is that there is some sort of an idle session timeout
>>>>>>> kicking in (most likely on the client side) and dropping the connection.
>>>>>> There's no such timeout in the Postgres server, for sure. I would
>>>>>> actually bet that your problem is in some router between the client and
>>>>>> the server. In particular, routers that do NAT address mapping
>>>>>> typically have a timeout after which they will forget the mapping for an
>>>>>> idle connection. If you've got one of those, see if it'll let you
>>>>>> change the timeout.
>>>>>>
>>>>>> If you can't do that, you might think about teaching your client-side
>>>>>> code to send dummy queries every so often.
>>>>>>
>>>>>> regards, tom lane
>>>>>>
>>>>> I've already maxed out the connection timeout at the firewall... and
>>>>> I've been using dummy queries every five minutes, but it just feels like
>>>>> a crutch to do so.
>>>> Have you looked into tcp keepalive settings?
>>>>
>>>> net.ipv4.tcp_keepalive_intvl = 75
>>>> net.ipv4.tcp_keepalive_probes = 9
>>>> net.ipv4.tcp_keepalive_time = 500
>>>>
>>>> Not sure if those settings will help with an NAT router or not but it's
>>>> worth a try. Pgsql 8.2 can set those for you.
>>>>
>>> I may be celebrating prematurely,
>> Never stopped me :)
>>
>>> but resetting the tcp_keepalive
>>> parameters seems to have done the trick - I left a pgAdmin connection
>>> that *always* drops after inactivity up while I went to lunch and it was
>>> still alive when I got back.
>>>
>>> Is there a way to alter the tcp_keepalive settings on an app-by-app
>>> basis rather than for the whole system?
>> Well, you could set it on individual workstations instead of on the
>> server. I.e. if you set tcp_keepalive on your workstation to 500, but
>> leave Wally and Dilbert set at the default 7200 then they'd still
>> timeout and you wouldn't.
>>
>
> Unfortunately, my individual workstations are all running Windows...:(
> any idea if/where this could be set in MS-land?
>

You need to alter some settings in the registry.

See: http://msdn2.microsoft.com/en-us/library/aa302363.aspx

--
Paul Lambert
Database Administrator
AutoLedgers