PostgreSQL查询在应用服务器关闭时未终止 [英] PostgreSQL queries not killed on app server shutdown
问题描述
我有一个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.
是什么原因造成的?谁在SQL 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.
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.
这篇关于PostgreSQL查询在应用服务器关闭时未终止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
