Re: pg_stat_activity doubts

Thanks Ray .

Any thoughts on my query #3?

On Thu 22 Mar, 2018, 7:13 PM Ray Stell, <stellr(at)vt(dot)edu> wrote:

> On 3/22/18 8:45 AM, Debraj Manna wrote:
>
> Hi
>
> Can someone resolve my below doubts about pg_stat_activity :-
>
>
> 1. 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?
> 2. 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?
> 3. 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?
> 4.
>
>
> 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 |
>
>
>