Queries being executed at the moment in PostgreSQL
The SQL below can be used to check what database statements (selects, updates, inserts, etc.) are begin executed at the moment in PostgreSQL database. It shows queries grouped by client application, user, and query max start time (i.e. when the query was started last time). Some additional filtering is applied as well. You can play with other grouping options and filter conditions as you like in your particular scenarious.
select
application_name as "app",
usename as "user",
max(query_start) as "max_query_start",
count(*) as "query_count",
query
from
pg_stat_activity
where
1=1
and lower(query) not like '<insufficient privilege>'
and lower(query) not like ''
and lower(query) not like 'show %'
and lower(query) not like 'set %'
and lower(query) not like 'commit%'
group by
application_name, usename, query
order by
application_name, usename, max(query_start), query
;
Results will look like:
app |user |max_query_start |query_count |query |
-------------------------------------|---------|--------------------|------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|app_user |2017-05-24 17:04:03 |2 |SELECT COUNT(*) FROM pg_namespace WHERE nspname=$1 |
|app_user |2017-05-24 18:59:39 |1 |select * from Document where signature is null |
DBeaver 4.0.1 - Metadata |app_user |2017-05-24 18:59:34 |1 |SELECT DISTINCT x.oid,x.proname,x.pronamespace FROM pg_catalog.pg_proc x WHERE x.proname ILIKE $1 AND x.pronamespace IN ($2,$3) ORDER BY x.proname LIMIT 10 |
DBeaver 4.0.1 - SQLEditor <test.sql> |app_user |2017-05-24 18:59:45 |1 |select¶ application_name as "app",¶ usename as "user",¶ max(query_start) as "max_query_start",¶ count(*) as "query_count",¶ query¶from¶ pg_stat_activity¶where¶ 1=1¶ and lower(query) not like '<insufficient privilege>'¶ and lower(query) not like |