From 4f42b546fd87a80be30c53a0f2c897acb826ad52 Mon Sep 17 00:00:00 2001 From: Magnus Hagander Date: Thu, 19 Jan 2012 14:19:20 +0100 Subject: Separate state from query string in pg_stat_activity This separates the state (running/idle/idleintransaction etc) into it's own field ("state"), and leaves the query field containing just query text. The query text will now mean "current query" when a query is running and "last query" in other states. Accordingly,the field has been renamed from current_query to query. Since backwards compatibility was broken anyway to make that, the procpid field has also been renamed to pid - along with the same field in pg_stat_replication for consistency. Scott Mead and Magnus Hagander, review work from Greg Smith --- doc/src/sgml/monitoring.sgml | 233 ++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 219 insertions(+), 14 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index a12a9a2b726..225918019d6 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -242,20 +242,20 @@ postgres: user database host - pg_stat_activitypg_stat_activity - One row per server process, showing database OID, database - name, process ID, user OID, user name, application name, - client's address, host name (if available), and port number, times at - which the server process, current transaction, and current query began - execution, process's waiting status, and text of the current query. - The columns that report data on the current query are available unless - the parameter track_activities has been turned off. - Furthermore, these columns are only visible if the user examining - the view is a superuser or the same as the user owning the process - being reported on. The client's host name will be available only if - is set or if the user's host name - needed to be looked up during pg_hba.conf - processing. + + pg_stat_activity + pg_stat_activity + + + One row per server process, showing information related to + each connection to the server. Unless the + parameter has been turned + off, it is possible to monitor state and query information of + all running processes. + + + See for more details. + @@ -529,6 +529,210 @@ postgres: user database host + + pg_stat_activity view + + + + + Column + Type + Description + + + + + + datid + oid + The oid of the database the backend is connected to. + + + datname + name + The name of the database the backend is connected to. + + + pid + integer + The process ID of the backend. + + + usesysid + oid + The id of the user logged into the backend. + + + usename + name + The name of the user logged into the backend. + + + application_name + text + The name of the application that has initiated the connection + to the backend. + + + client_addr + inet + The remote IP of the client connected to the backend. + If this field is not set, it indicates that the client is either: + + + + Connected via unix sockets on the server machine + + + + An internal process such as autovacuum + + + + + + client_hostname + text + + If available, the hostname of the client as reported by a + reverse lookup of client_addr. This field will + only be set when is enabled. + + + + client_port + integer + + The remote TCP port that the client is using for communication + to the backend, or NULL if a unix socket is used. + + + + backend_start + timestamp with time zone + + The time when this process was started, i.e. when the + client connected to the server. + + + + xact_start + timestamp with time zone + + The time when the current transaction was started. If the client is + using autocommit for transactions, this value is equal to the + query_start column. + + + + query_start + timestamp with time zone + + The time when the currently active query started, or if + state is idle, when the last query + was started. + + + + state_change + timestamp with time zone + The time when the state was last changed. + + + waiting + boolean + + Boolean indicating if a backend is currently waiting on a lock. + + + + state + text + + The state of the currently running query. + Can be one of: + + + active + + + The backend is executing a query. + + + + + idle + + + There is no query executing in the backend. + + + + + idle in transaction + + + The backend is in a transaction, but is currently not currently + executing a query. + + + + + idle in transaction (aborted) + + + This state is similar to idle in transaction, + except one of the statements in the transaction caused an error. + + + + + fastpath function call + + + The backend is executing a fast-path function. + + + + + disabled + + + This state indicates that + is disabled. + + + + + + + The waiting and state columns are + independent. If a query is in the active state, + it may or may not be waiting. If a query is + active and waiting is true, it means + that the query is being executed, but is being blocked by a lock + somewhere in the system. + + + + + + query + text + + The most recent query that the backend has executed. If + state is active this means the currently + executing query. In all other states, it means the last query that was + executed. + + + + +
+ + + Statistics Access Functions + Other ways of looking at the statistics can be set up by writing queries that use the same underlying statistics access functions as @@ -1264,6 +1468,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, + -- cgit v1.2.3