Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to view active query underlying cursor

Tags:

postgresql

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)

I see there is pg_cursors, but it is empty:

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

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)
like image 750
Lorrin Avatar asked Jul 01 '14 17:07

Lorrin


1 Answers

You can't.

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

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

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

like image 86
Craig Ringer Avatar answered Oct 15 '22 06:10

Craig Ringer