Is there any way to break lost or idle connections TCP/IP in the PostgreSQL 8.4 in a Windows Server 2008?

Question

I've asked: http://stackoverflow.com/questions/3366619/how-to-break-connections-tcp-ip-by-keepalive-postgresql-without-changing-anything

And now, I want to confirm:

My system (C#.NET, NHibernate and Active Record) is running with a database PostgreSQL 8.4 and a Windows Server. I need a way to break idle or lost TCP/IP connections and unblock data.

I can't tweak OS configs nor recompile the PostgreSQL. My system can run in Oracle 10g Express if needed! I need to know: Can I break idle conections without implement a new patch for PostgreSQL? Do I need to migrate my database to Oracle for that?

Thanks,

Answer 1

You're looking for pg_cancel_backend() or pg_terminate_backend() ?

Check http://www.postgresql.org/docs/current/static/functions-admin.html

Answer 2

If there's a way to force each new connection to execute a query at connect time, you could write a database function that dynamically calls "pg_terminate_backend(pid)" for any processes that are in some criteria. Without knowing your setup, some possibilities are:

• Connections with current_query = ' in transaction' and CURRENT_TIMESTAMP - query_start() greater than some value (1 minute? 5 minutes?)
• Connections from your IP address or individual user name (if exists) that you aren't currently using (probably not a good idea in connection-pooling environments, though)

Personally, I would try and refactor in order to minimize the amount of time any transaction can possibly be open. Some places where you may have a transaction open unnecessarily, resulting in an increased chance of triggering your problem:

• An ORM is running with AutoCommit turned off.
• A LOCK is taken when data is read for an edit screen so the data cannot be updated before it is written back. It may be acceptable to verify before writing back that there's no conflict.
• Transactions started before a non-database task, like an FTP or Email process, then closed afterwards.
• Transactions where the very first statement is a SELECT without a locking clause (like "FOR UPDATE"). The result of this SELECT will be the same whether it is inside or outside the transaction, so it's an indicator that the transaction was started too early and could be shortened.
• Transactions that only contain one statement of any kind.