Postgresql Tips n' Tricks
Table of Contents
1 pg_stat_activity
pg_stat_activity is a view that belongs to the postgresql catalog, it helps you to view the the status of the queries that are in execution, the explanation of each column can be found in the postgresql manual The Statistics Collector section (I don't want to do a simply copy-paste, so follow the link).
| Column | Type | Modifiers |
|---|---|---|
| datid | oid | |
| datname | name | |
| procpid | integer | |
| usesysid | oid | |
| usename | name | |
| currentquery | text | |
| waiting | boolean | |
| xact_start | timestamp with time zone | |
| query_start | timestamp with time zone | |
| backend_start | timestamp with time zone | |
| client_addr | inet | |
| client_port | integer |
SELECT * FROM pg_stat_activity;
- If you want to view the queries that are stuck (usually due to a lock or simply waiting for I/O) you can execute the following query:
SELECT * FROM pg_stat_activity WHERE waiting = TRUE;
Generated by Org version 7.5 with Emacs version 23 on 2010-09-01 Wed
Copyright © 2011 tty team
Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, with no Front-Cover Texts, and with no Back-Cover Texts. A copy of the license is included in the section entitled “GNU Free Documentation License”.