I search for a way to control the session timeout of the PGSQL (9.0) client (Windows).

When a Session dying? What happened with them after die.

How can I force a Session to die? (For example it is "locked", on some wrong long query, and I want to force the server to release the resources).

Thanks for it: dd


I extend this to understand it: The databases need to know which session is dead. Dead session must be released, because it is only hold the resources, and if this operation not finished, many locks we should get, or we can out of available connections (reach the maximum).

Other DataBases (FireBird, EDB) defines a TimeOut parameter for it.

When it reached, the session set to dead, and user connection aborted.

To avoid exhausting you need to periodically do something, that extend the period.

Theres is 3 ways to reach the timeout: 1.) the client program hangs, or freezed, or closed. 2.) the network connection broken 3.) the client send some very long query/stored procedure that don't finish.

If the timeout not handled by server, may somebody's transaction, lock, etc still alive for X hours, and you have to only one way to remove it: restart the db server service.

Other databases handle dead sessions as they no more interact to the server, so the client got some error, it need to restart the client software.

Some databases supports the return to the "inactive" but "not dead" session, and they can continue the work.

So, with this preface I ask my question again:

  1. How can I control the client's session timeout under pgsql? System variable, SQL parameter, etc?
  2. How can I extend this time?
  3. What happens if a long query is exhausting the period?
  4. When does the pgsql server release the resources held by the client ?

Thanks: dd

Comments

I'm not aware of such settings in Postgres. To me this sounds like you'd better fix the connection handling in your application. The only "solution" I can think of, is to use a connection pooler (like pg-pool or pgBouncer) that sits between your application and the database. As it acts as a proxy, the application won't be aware of that.

Accepted Answer

I don't understand the first part of your question, but to kill a running session you can use pg_terminate_backend()

To kill the query of a running session use pg_cancel_query()

Both functions are explained in the manual:

http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE

This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki