PostgreSQL查询在应用服务器关闭时未终止

本文介绍了PostgreSQL查询在应用服务器关闭时未终止的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个WildFly，它托管着一个应用程序，该应用程序正在调用一些长时间运行的SQL查询(例如，查询或SP调用需要10到20分钟或更长时间).

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).

以前，此WildFly指向SQL Server 2008，现在指向Postgres11.

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

以前，当我杀死/重新启动WildFly时，我注意到(即使不是瞬间)从Java代码触发的长期运行的SP/查询调用(在WildFly中运行)也被杀死了.

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.

现在...使用Postgres，我注意到这些长时间运行的查询仍然存在，即使在关闭应用服务器后，它们仍在Postgres中运行.

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.

是什么原因造成的?谁在S​​QL Server(服务器端本身或JDBC驱动程序或...)中杀死它们? Postgres中是否有一些设置/参数可以控制此行为，即，如果已关闭触发查询的客户端，则DB服务器将对查询执行什么操作.

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.

通过向WF发送命令以自行关闭，我们可以正常关闭WildFly. SQL Server和Postgres之间的行为似乎仍然有所不同.

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.

推荐答案

如果关闭应用程序服务器导致JDBC调用终止数据库会话，则不应发生这种情况.如果无法正确关闭JDBC连接，我将其称为应用程序服务器中的错误.如果可以，但是后端的查询没有取消，我将其称为JDBC驱动程序中的错误.

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.

无论如何，一种解决方法是设置 tcp_keepalives_idle 设置为较低的值，以便服务器快速检测到无效的TCP连接并终止查询.

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.