Re: When the Session ends in PGSQL?

On Monday 04 July 2011 10:48:48 Durumdara wrote:
> 1.) DataBases need to close the resources kept by Sessions.
> 2.) There is need some "border", or a rule to split - which Session is
> considered as "finished".

So far so good.

> The FireBird is uses "DummyPacketInterval" to recognize dead sessions,
> EDB uses "Session Timeout" value for it.
> The EDB can still alive if network connection lost, if before Timeout
> the client also send a sign to the server.
>
> To I can use PGSQL also, I need to know the limitations, and how to
> handle the connections, how to manage them.
>
> I wondering to PGSQL handle this with living TCP connections, because
> this is may out of our control.
> If we have a timeout value, we can control, which time we have, and
> what time is acceptable for a Session.
> For example: some places we have that uses wifi connections are
> sometimes broken for just a little period. This is enough to
> disconnect, but because of higher "Session Timeout" variable our
> DataBase connections still alive without close the applications.
>
> Another thing is sign (packet). We must do something periodically to
> keep alive the connection. For example: every 1 minutes we do some
> dummy thing one server, like "select date" or etc.

AFAIK postgres doesn't distinguish between a "TCP session" and a "database
session" like (if I understand you correctly) FireBird/EDB does. You cannot
reconnect and say "hello it's me again from session FOOBAR, can I resume that
session ?". I believe you'll have to solve this at the application layer :

* Make transactions, locks, temp tables, etc as short-lived as possible (this
   is always a good thing to do anyway).
* If that's still not enough, store your "current working state" in a purpose-
   built table and add logic in your client to reinitialize session state
   using that data, and to purge the data after it has been used / timed out.

Another thing you could do (but I'm not sure it is a good idea) is to write a
proxy application that runs on the server machine and understands your session
requirements. Then connect your application to this proxy instead of the
database.

--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.