PostgreSQL 如何调试/追踪 ‘在事务中闲置’ 的连接

在本文中，我们将介绍如何调试和追踪 PostgreSQL 数据库中的 ‘在事务中闲置’（Idle in transaction）连接。’Idle in transaction’ 是指一个连接已经处于一个事务中，但是在该事务中没有执行任何操作，而是处于闲置状态。这种连接可能会造成数据库性能问题，因此需要及时调试和追踪。

阅读更多：PostgreSQL 教程

了解 ‘Idle in transaction’ 连接

在 PostgreSQL 中，’Idle in transaction’ 连接通常发生在一个客户端连接执行了一个 BEGIN 语句，但是之后没有执行任何其他的 SQL 语句。例如，一个应用程序在执行 BEGIN 事务后出现异常终止或崩溃，而没有执行 COMMIT 或 ROLLBACK 操作，那么连接将会处于 ‘Idle in transaction’ 状态。

‘Idle in transaction’ 连接可能会导致以下问题：

资源占用：’Idle in transaction’ 连接会占用数据库连接池中的资源，而这些资源本可以被其他活跃的连接使用。
阻塞：当 ‘Idle in transaction’ 连接持续存在时，可能会导致其他客户端连接的执行出现延迟或阻塞。
性能下降：由于额外的连接数量和资源占用，数据库的整体性能可能会受到影响。

调试 ‘Idle in transaction’ 连接

要调试 ‘Idle in transaction’ 连接，我们可以通过以下步骤进行：

查找 ‘Idle in transaction’ 连接：可以通过查询数据库的 system view pg_stat_activity 来查找所有当前活动的连接。使用以下 SQL 查询可以找到所有处于 ‘Idle in transaction’ 连接的连接：
```
SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
```
SQL
找出引起 ‘Idle in transaction’ 连接的原因：在找到 ‘Idle in transaction’ 连接后，可以通过分析该连接的相关信息来确定引起问题的原因。可以查看其当前事务的开始时间、当前执行的 SQL 语句等。
解决问题并断开连接：根据找到的问题原因，采取相应的措施来解决问题并断开 ‘Idle in transaction’ 连接。例如，可以执行 COMMIT、ROLLBACK 或者强制断开连接的操作。

追踪 ‘Idle in transaction’ 连接

如果 ‘Idle in transaction’ 连接仍然持续出现，我们可以采取以下方法进行追踪：

使用日志追踪：可以通过在 PostgreSQL 配置文件中启用详细的日志记录来捕获 ‘Idle in transaction’ 连接的相关信息。在配置文件中设置 log_statement = ‘all’ 可以记录所有的 SQL 语句，以便后续分析和调试。
使用 pg_stat_statements 插件：pg_stat_statements 是一个 PostgreSQL 扩展插件，可以跟踪和统计所有的 SQL 语句执行情况。通过查看 pg_stat_statements 视图，可以找到执行时间较长的 SQL 语句，从而判断是否存在引起 ‘Idle in transaction’ 连接的潜在问题。
使用 pg_stat_activity 视图：pg_stat_activity 视图提供了关于当前活动连接的详细信息。可以通过查询该视图来获取 ‘Idle in transaction’ 连接的相关信息，并进一步分析和追踪。

示例说明

假设我们有一个名为 “testdb” 的 PostgreSQL 数据库，我们要调试和追踪其中的 ‘Idle in transaction’ 连接。首先，我们可以通过以下 SQL 查询找到所有处于 ‘Idle in transaction’ 状态的连接：

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';

然后，我们可以进一步分析这些连接的事务开始时间和当前执行的 SQL 语句来找出问题的原因。假设我们发现某个连接在执行一个较长时间的 SQL 语句后进入了 ‘Idle in transaction’ 状态，我们可以使用 pg_stat_statements 插件来追踪该 SQL 语句的执行情况：

SELECT query, total_time FROM pg_stat_statements WHERE query = '<长时间的SQL语句>';

通过对长时间的 SQL 语句进行分析，我们可以找到潜在的性能问题，并采取相应的措施来解决问题并断开 ‘Idle in transaction’ 连接。

总结

在本文中，我们讨论了如何调试和追踪 PostgreSQL 数据库中的 ‘Idle in transaction’ 连接。’Idle in transaction’ 连接可能会占用资源、导致阻塞和性能下降，因此需要及时处理。通过查找和分析连接的相关信息，我们可以找出 ‘Idle in transaction’ 连接的原因，并采取相应的解决措施。同时，使用日志追踪和扩展插件可以进一步追踪和分析连接的执行情况，以便找出潜在的性能问题。