PostgreSQL queries not killed on app server shutdo

问题:

I have a WildFly which hosts an app which is calling some long running SQL queries (say queries or SP calls which take 10-20 mins or more).

Previously this WildFly was pointing to SQL Server 2008, now to Postgres 11.

Previously when I killed/rebooted WildFly, I had noticed that pretty quickly (if not instantaneously) the long-running SP/query calls that were triggered from the Java code (running in WildFly) were being killed too.

Now... with Postgres I noticed that these long running queries remain there and keep running in Postgres even after the app server has been shut down.

What is causing this? Who was killing them in SQL Server (the server-side itself or the JDBC driver or ...)? Is there some setting/parameter in Postgres which controls this behavior i.e. what the DB server will do with the query provided that the client who triggered the query has been shut down.

EDIT: We do a graceful WildFly shutdown by sending a command to WF to shutdown itself. Still the behavior seems different between SQL Server and Postgres.

回答1:

If shutting down the application server causes JDBC calls that terminate the database session, this should not happen. If it doesn't close the JDBC connection properly, I'd call that a bug in the application server. If it does, but the queries on the backend are not canceled, I'd call that a bug in the JDBC driver.

Anyway, a workaround is to set tcp_keepalives_idle to a low value so that the server detects dead TCP connections quickly and terminates the query.