Skip site navigation (1) Skip section navigation (2)

Re: When the Session ends in PGSQL?

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: Durumdara <durumdara(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: When the Session ends in PGSQL?
Date: 2011-06-30 15:06:36
Message-ID: 20110630110636.9516c6d6.wmoran@potentialtech.com (view raw or flat)
Thread:
Lists: pgsql-general
In response to Durumdara <durumdara(at)gmail(dot)com>:

> Hi!
> 
> I asked same thing in the list formerly,
> http://archives.postgresql.org/pgsql-general/2011-06/msg00888.php
> 
> And because of lack of answers now I asked here also:
> http://stackoverflow.com/questions/6534654/postgresql-session-timeout
> 
> I'm very wondering that I don't (and can't) found a system variable
> that define the timeout of the client session. Maybe I'm a real lama,
> or search with wrong terms... :-)
> 
> The manual often refers to "Session end" - when the resources
> released, but never I saw a section that describe when it happens.
> 
> The RDBMS-s needs some rule to mark a Session inactive or/and dead.
> 
> Commonly they are uses a "Timeout" parameter that control how many
> minutes has ellapsed to session marked as Dead.
> 
> FireBird and ElevateDB do this too. If the client is do anything, this
> time period extends again.
> 
> The cleanup is needed for remove the locks, unfinished (limbo or
> started) transactions, and decrease the deadlock, or lock situations;
> and to never reach the maximum connections.
> 
> So: is anybody here that can tell me how this working in PGSQL?
> 
> And if there is no control, when the Session ends?
> 
> Practical example (real):
> A client program died on a transaction, and it leaves many locks in
> the DataBase.
> 
> If Session Timeout is 5 minutes, then we can tell the other users who
> got "Row lock", or "Transaction Timeout" errors to wait 6-7 minutes
> and then trying again, because after 5 minutes the problematic session
> is cleaned up, and locks/transactions removed from the objects.

Session ends when the connection is terminated.  I.e., a PostgreSQL
session is synonymous with a TCP connection (or domain socket in some
cases).

There is no setting in PostgreSQL to pro-actively terminate connections.
Such an option wouldn't make any sense to include, if you ask me.  I
mean, if I open a psql and start a transaction, then get interrupted or
have to spend some time researching something else before I can finish,
the last thing I want is to come back to psql and find that my session
was terminated and my in-progress data changes lost.

For your concern about dying applications, the OS will tear down the
TCP/IP connection eventually, which will result in PG ending the
session (rolling back and uncommitted transaction), so that approach
doesn't really cause you problems there.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to

Responses

pgsql-general by date

Next:From: gnuoytrDate: 2011-06-30 15:17:14
Subject: Re: Contrib source
Previous:From: Reid ThompsonDate: 2011-06-30 15:04:43
Subject: Re: Contrib source

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group