postgresql - 如何在事务中获取当前查询

Question

我试图从 pg_stats_activity 获取当前查询，但没有按预期工作。

在交易之外一切正常:

pagetest=# select query from pg_stat_activity where pid = pg_backend_pid() and 1 is not null;
                                       query                                        
------------------------------------------------------------------------------------
 select query from pg_stat_activity where pid = pg_backend_pid() and 1 is not null;
(1 row)

pagetest=# select query from pg_stat_activity where pid = pg_backend_pid() and 2 is not null;
                                       query                                        
------------------------------------------------------------------------------------
 select query from pg_stat_activity where pid = pg_backend_pid() and 2 is not null;
(1 row)

pagetest=# select query from pg_stat_activity where pid = pg_backend_pid() and 3 is not null;
                                       query                                        
------------------------------------------------------------------------------------
 select query from pg_stat_activity where pid = pg_backend_pid() and 3 is not null;
(1 row)

但是在交易中我得到了错误的结果:

pagetest=# begin;
BEGIN
pagetest=# select query from pg_stat_activity where pid = pg_backend_pid() and 1 is not null;
                                       query                                        
------------------------------------------------------------------------------------
 select query from pg_stat_activity where pid = pg_backend_pid() and 1 is not null;
(1 row)

pagetest=# select query from pg_stat_activity where pid = pg_backend_pid() and 2 is not null;
                                       query                                        
------------------------------------------------------------------------------------
 select query from pg_stat_activity where pid = pg_backend_pid() and 1 is not null;
(1 row)

pagetest=# select query from pg_stat_activity where pid = pg_backend_pid() and 3 is not null;
                                       query                                        
------------------------------------------------------------------------------------
 select query from pg_stat_activity where pid = pg_backend_pid() and 1 is not null;
(1 row)

pagetest=# rollback
pagetest-# ;
ROLLBACK

看起来统计收集器 View 上有一些奇怪的可见性规则。有什么合理的解决方法吗？

Answer 1

试试这个:

  BEGIN;
    select 1,current_query();
    select 2,current_query();
   ROLLBACK;