Re: When the Session ends in PGSQL?

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/