如何列出所有活动会话的所有准备好的语句？ [英] How to list all prepared statements for all active sessions?

问题描述

我知道可以通过从 pg_prepared_statements 表中选择所有行来列出当前会话的所有准备好的语句，但是有一种方法可以查看所有准备好的语句所有活动会话的声明？我想我正在寻找类似管理员功能的东西，但是我在文档中找不到类似的东西。

I know that there is a way to list all prepared statements for the current session by selecting all rows from the pg_prepared_statements table, but is there a way to see all prepared statements for all active sessions? I think I'm looking for something like an administrator function, but I can't find anything like it in the docs.

推荐答案

不。 AFAIK准备的语句在后端是本地的；其他后端只是不知道它们存在。您需要修改服务器以添加其他进程间通信，以允许一个后端向其他后端询问准备好的语句。

Nope. AFAIK prepared statements are local to a backend; other backends just don't know they exist. You'd need to modify the server to add additional inter-process communication to allow one backend to ask the others about prepared statements.

后端最初似乎共享相同的 pg_prepared_statements 表存储，例如：

The backends initally appeared to share the same pg_prepared_statements table storage, as:

SELECT relfilenode FROM pg_class WHERE  relname = 'pg_prepared_statements';

从不同的后端返回相同的relfilenode。我感到惊讶，因为我认为准备好的语句没有磁盘上的内容。如果它们在磁盘上，我想您可以使用 pageinspect贡献模块中的功能a>读取原始元组或表页面。可见性将是一个问题；

returns the same relfilenode from different backends. I was surprised, as I thought prepared statements had no on-disk presence. If they were on disk I guess you could use the functions from the pageinspect contrib module to read the raw tuples or table pages. Visibility would be a problem; how would you know what was related to a dead/terminated backend, and what was valid?

我尝试了一下，发现 pg_prepared_statements code>实际上是一个视图：

I tried that, and found that pg_prepared_statements is actually a view:

regress=# SELECT * FROM heap_page_items(get_raw_page('pg_prepared_statements', 1));
ERROR:  cannot get raw page from view "pg_prepared_statements"

特别是在 pg_prepared_statement（）函数。因此，无需检查。它是内部后端。

specifically a view over the pg_prepared_statement() function. So there's nothing to inspect. It's backend internal.

这似乎是其中之一，但是为什么要这么做？问题，这通常表示有人在询问他们对实际问题设想的解决方案，而不是询问其实际问题。

This seems like one of those "... but why would you do that?" questions, which is often a sign that someone is asking about a solution they've envisioned to their real problem, rather than asking about their real problem.

所以：你为什么要那样？您要解决的真正问题是什么？

So: Why do you want that? What's the real problem you're trying to solve?

这篇关于如何列出所有活动会话的所有准备好的语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！