CODEWITHSUNDEEP
postgresql
David Tinker
David Tinker

Reputation: 9634

How can I see schema for queries in pg_stat_activity?

I am trying to sort out some app performance issues with slow queries. We are using Postgresql 9.2. I can see the queries in progress easily:

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 what schema the tables listed in the query are in. How can I see which "product" table is being queried given that there are hundreds of them in different schemas in the same database.

Upvotes: 5

Views: 2075

Answers (2)

user357811
user357811

Reputation: 1

AFAIK If the query you are interested in has locks, you may find out its schema oid from pg_locks and then the schema name from pg_namespace.

Upvotes: 0

klin
klin

Reputation: 121594

pg_stat_activity view calls pg_stat_get_activity(pid int) internal function. You have no chances to change query text in result. There is only one solution for now - call your queries with schema names:

select bla from myschema.product where ...

Upvotes: 1

Related Questions