How to get current query inside a transaction

Question

I tried to get the current query from pg_stats_activity but that didn't work as expected.

Outside a transaction everything works:

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)

But inside a transaction I get the wrong result:

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

It looks like there are some strange visibility rules on the stat collector views. Is there any reasonable way around this?

Answer 1

try this :

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