Re: When the Session ends in PGSQL?

In response to Durumdara <durumdara@gmail.com>:

> Hi!
>
> 2011/6/30 Bill Moran <wmoran@potentialtech.com>:
> > In response to Durumdara <durumdara@gmail.com>:
> >
> > Session ends when the connection is terminated.  I.e., a PostgreSQL
> > session is synonymous with a TCP connection (or domain socket in some
> > cases).
>
> In Windows OS what's this meaning?
> I know there is a difference between Win's and Lin's TCP handling.

There's differences between TCP handling in different distros of Linux,
but it doesn't change the simple fact that all OSes will kill dead
sockets eventually, and when the socket is closed, the PG session ends.

> > Such an option wouldn't make any sense to include, if you ask me.
>
> In EDB and FireBird we experienced that Timeout is good thing for
> avoid the short net problems.
> For example: many place they are using notebooks, and wifis.
> If somebody get out the wifi area, lost the connection - but the wifi
> CAN reactivate it when he/she is go again the needed place. And here
> the DB service also can reactivate the Session in the background -
> except the period exhaustion.

All of that can be done with the correct settings at the TCP level as
well.

> > 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.
>
> Maybe in PSQL, but not in client applications where users working, and
> sometimes they are got some failures, like power, or blue screen, or
> application dying.
> They want to continue their's work, but when the locks/trs are
> reamining they cannot do it.

Incorrect.  If a Windows client bluescreens, the server will eventually
notice that the socket is dead and clean it up.  If that's taking too
long, then you need to review the TCP settings on your server.

However, it sounds to me like your application is poorly designed.  If
it's being used via unreliable connections, but requires that database
locks be held for any length of time, you've got two warring design
requirements, and one or the other is always going to suffer.

> > 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.
>
> Uhhhhhh... This sounds awfully.
>
> Is this meaning that I periodically lost my temp tables, locks,
> transactions because of OS's network handling what is out of my
> control?
>
> It sounds horrible for me. When this thing happens commonly?

You are the first person I've had complain that this is a common
scenario with database applications.  It sounds like your application
was not designed properly to take into account the various demands of
the environment.

Keeping things moving along happily in a situation where disconnects
are frequent and unpredictable is damn difficult.  If you were to add
a "session timeout" variable, you'd find that you haven't fixed the
problem, only caused it to manifest in different ways.  Additionally,
that's not a problem that it's in PostgreSQL's best interest to try to
solve, as it's really closer to the application level (from a requirement
perspective).

> I must sure in my Session still alive, and to keeps my important temp
> tables, locks, etc in safely place...

If "temp tables" are "important", then you're doing it wrong.  Using
temp tables for any data that is not completely sacrificial is a poor
design decision that is liable to haunt you in many ways.  It destroys
your ability to use connection pooling, replication, failover, etc in
addition to the problem you are currently complaining about.

Locks are the same way, keeping DB locks in place for extended periods
is almost always a bad idea, and not really the purpose of RDBMS-
level locking.  Look into implementing advisory locking at the application
level.

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