How can I see the schema for queries in pg_stat_activity?

Question

I am trying to deal with some performance issues with slow query applications. We are using Postgresql 9.2. I can easily see that the requests are being executed:

postgres=# select now() - query_start, query from pg_stat_activity where state <> 'idle'; 00:00:01.535388 | select bla from product where ... 

I need to know which schema contains the tables listed in the query. How can I see which “product” table is being requested, given that there are hundreds of them in different schemas in the same database.

Answer 1

pg_stat_activity view functions pg_stat_get_activity (pid int) . You have no chance to change the query text as a result. At the moment, there is only one solution - call your queries with schema names:

 select bla from myschema.product where ...