I usually prefer pg_cancel_backend because it seems.. nicer, but lately I've had a troublesome user who leaves transactions open and I've scripted up a call to pg_terminate_backend after 60 minutes in an idle transaction. It works well.
Question: is there any server impact beyond that user's transaction when calling pg_terminate_backend?
I would advise against doing that for application connections. What happens depends on the application or pooling tech used. It could generate errors in the application as the connection is no longer valid yet the application might still being maintaining data structures for the given connection.
I.e.. It also introduces a race condition; the application picks up the connection from the connection pool at the same time the connection is terminated on the backend; the end result is an error propagating up to end user or the application being require to catch the error and retry, etc.
I would continue to terminate non application connections without concern. If it’s an issue, fix the application/pooling config; terminating backend sessions is a bandaid with other issues.
One more thought if it wasn’t apparent already. Use TCP keep alive -- to identify truly abandoned idle sessions and have those connection torn down.
