如何查看活动查询基础游标 [英] How to view active query underlying cursor

问题描述

在 pg_stat_activity 中，我可以看到客户端正在使用游标通过某些查询结果进行工作。但是，如何查看原始查询是什么？

In pg_stat_activity I can see that a client is working its way through some query results using a cursor. But how can I see what the original query is?

pipeline=> select pid, query from pg_stat_activity where state = 'active' order by query_start;
  pid  |                                                         query
-------+--------------------------------------------------------------------------------------
  6734 | FETCH FORWARD 1000 FROM "c_109886590_1"
 26731 | select pid, query from pg_stat_activity where state = 'active' order by query_start;
(2 rows)

我看到有 pg_cursors ，但为空：

pipeline=> select * from pg_cursors;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

服务器位于AWS RDS上。

The server is on AWS RDS.

pipeline=> select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3 20120306 (Red Hat 4.6.3-2), 64-bit
(1 row)

推荐答案

您不能。

pg_cursors 是后端本地的。它不会显示不属于当前连接的游标。

pg_cursors is backend-local. It doesn't show cursors that aren't part of the current connection.

PostgreSQL无法找出来自另一个会话的游标背后的查询。

PostgreSQL has no way to find out what query underlies a cursor from another session.

我唯一想到的方法是使用日志分析，其中 log_statement = all 和一个合适的 log_line_prefix 。

The only way I can think of to do this is using log analysis, with log_statement = all and a suitable log_line_prefix.

这篇关于如何查看活动查询基础游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！