Thanks Ray .
Any thoughts on my query #3?
On Thu 22 Mar, 2018, 7:13 PM Ray Stell, <stellr@xxxxxx> wrote:
On 3/22/18 8:45 AM, Debraj Manna wrote:
Hi
Can someone resolve my below doubts about pg_stat_activity :-
- Can someone let me know how pg_stat_activity is populated? If I am executing a query like `Select * from pg_stat_activity` does it produce the details from some snapshot maintained by postgres or it checks the current connections open in postgres to provide the details?
- Can some let me know what does the state IDLE denotes in the row of pg_stat_activity? Is it like it checks all the open connections to postgres and if the connection is not executing any query then IDLE denotes the last query executed by the connection?
- In the output of select * from pg_stat_activity I am seeing rows with state IDLE and the query_start_date more than 2 days old? What does this signify ? Should I be worried about this?
You can use psql -E to trace the source :
template1=# \d pg_stat_activity
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(pg_stat_activity)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '11283';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
NULL AS indexdef,
NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '11283' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
state | text |
backend_xid | xid |
backend_xmin | xid |
query | text |
