PostgreSQL pg_stat_activity
What is pg_stat_activity?
pg_stat_activity is a PostgreSQL system view that is a good first place to start when you want to find out what is going on with your PostgreSQL installation.
Aside 1
If you need to make a connection to a PostgreSQL installation, a good first place to start is
username postgres
dbname postgres
Any default PostgreSQL install will have both this user and database. Tool makers know this convention, and design tools that connect using these defaults.
Aside 2
Here's how you list all the databases in a PostgreSQL cluster:
mwood@mwod-ThinkPad-X220:~$ psql -U postgres -l
List of databases
┌───────────┬──────────┬──────────┬─────────┬───────┬───────────────────────┐
│ Name │ Owner │ Encoding │ Collate │ Ctype │ Access privileges │
├───────────┼──────────┼──────────┼─────────┼───────┼───────────────────────┤
│ postgres │ postgres │ UTF8 │ C │ C │ │
│ pqgotest │ postgres │ UTF8 │ C │ C │ │
│ template0 │ postgres │ UTF8 │ C │ C │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8 │ C │ C │ =c/postgres ↵│
│ │ │ │ │ │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴─────────┴───────┴───────────────────────┘
(4 rows)
(A PostgreSQL installation is generally called a cluster.)
Let's make three connections to PostgreSQL:
terminal1: $ psql -U postgres -d postgres terminal2: $ psql -U postgres -d postgres terminal3: $ psql -U postgres -d postgres
Determine number of active connections to the cluster:
postgres@[local]:5432/postgres# select count(*) from pg_stat_activity; ┌───────┐ │ count │ ├───────┤ │ 3 │ └───────┘ (1 row)
Show which database each connection is using:
postgres@[local]:5432/postgres# select datname from pg_stat_activity; rollback; ┌──────────┐ │ datname │ ├──────────┤ │ postgres │ │ postgres │ │ postgres │ └──────────┘ (3 rows)
Identify our connections using the set command.
Terminal 1 postgres@[local]:5432/postgres# set application_name to worker1; SET Terminal 2 postgres@[local]:5432/postgres# set application_name to worker2; SET Terminal 3 postgres@[local]:5432/postgres# set application_name to worker3; SET
Now we can identify or workers in pg_stat_activity:
postgres@[local]:5432/postgres# select application_name from pg_stat_activity; ┌──────────────────┐ │ application_name │ ├──────────────────┤ │ worker3 │ │ worker2 │ │ worker1 │ └──────────────────┘ (3 rows)
set application_name often
For long-lived connections, (re)setting application name can reveal useful state information:
Terminal 1 postgres@[local]:5432/postgres# set application_name = 'worker1 on job 31'; SET Terminal 2 postgres@[local]:5432/postgres# set application_name = 'worker2 on job 32'; SET Terminal 3 postgres@[local]:5432/postgres# set application_name = 'worker3 on job 33'; SET
That's better
postgres@[local]:5432/postgres# select application_name from pg_stat_activity; rollback; ┌───────────────────┐ │ application_name │ ├───────────────────┤ │ worker3 on job 33 │ │ worker2 on job 32 │ │ worker1 on job 31 │ └───────────────────┘ (3 rows)
Aside: The set command
The set command has both set foo to and set foo = variants.
The opposite of set is show:
Terminal 1 postgres@[local]:5432/postgres# show application_name; ┌───────────────────┐ │ application_name │ ├───────────────────┤ │ worker1 on job 31 │ └───────────────────┘ (1 row)
'show all' will show all the settings for your session (there are 243 settings as of PostgreSQL 9.4.4!)
Let's disable autocommit for our three sessions
Terminal 1 postgres@[local]:5432/postgres# \set AUTOCOMMIT off Terminal 2 postgres@[local]:5432/postgres# \set AUTOCOMMIT off Terminal 3 postgres@[local]:5432/postgres# \set AUTOCOMMIT off
what is AUTOCOMMIT?
- SET AUTOCOMMIT is an extension of PostgreSQL ECPG
- \set is a meta-command used to set psql variables. It is client-side (not server-side) and specific to the psql client.
- SET AUTOCOMMIT sets the autocommit behavior of the current database session. By default, embedded SQL programs are not in autocommit mode, so COMMIT needs to be issued explicitly when desired. This command can change the session to autocommit mode, where each individual statement is committed implicitly.
When we say
postgres@[local]:5432/postgres# \set AUTOCOMMIT off
we are telling psql to take its connection out of autocommit mode.
Aside: cool psql prompts using \set
This is the \set command to create the psql prompts you see in this demonstration:
-- [user]@[host]:[port]/[db] -- ['*' if we are in a transaction] -- ['#' if we are root-like; '>' otherwise] \set PROMPT1 '%n@%m:%>/%/%x%# '
Later in the demo, the appearance of * at the prompt for non-committed transactions will be important.
Let's create a table
postgres@[local]:5432/postgres#
create table t (
id int constraint t_pk primary key not null,
name text not null);
commit;
CREATE TABLE
COMMIT
postgres@[local]:5432/postgres#
insert into t (id, name)
values (1, 'foo');
commit;
INSERT 0 1
COMMIT
Let's update the same row from two connections
Terminal 1
postgres@[local]:5432/postgres# update t set name = 'bar' where id = 1;
UPDATE 1
postgres@[local]:5432/postgres*# <-- prompt shows uncommited transaction!
Note that we have NOT committed!
Terminal 2
postgres@[local]:5432/postgres# update t set name = 'zzz' where id = 1;
<-- blocked; we did not get a prompt back!
Let's use Terminal 3 to investigate what is happening.
postgres@[local]:5432/postgres#
select application_name,
state,
waiting,
query
from pg_stat_activity; rollback;
┌───────────────────┬─────────────────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ application_name │ state │ waiting │ query │
├───────────────────┼─────────────────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ worker3 on job 33 │ active │ f │ select application_name, state, waiting, query from pg_stat_activity; │
│ worker2 on job 32 │ active │ t │ update t set name = 'zzz' where id = 1; │
│ worker1 on job 31 │ idle in transaction │ f │ update t set name = 'bar' where id = 1; │
└───────────────────┴─────────────────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
(3 rows)
Worker 2 is blocked waiting on worker 1
Let's pretend worker 1 is stuck
Worker 1 is holding open its transaction, but doing nothing with it!
Let's get the pid of worker 1's connection:
Terminal 3
postgres@[local]:5432/postgres#
select pid,
application_name
from pg_stat_activity
where state = 'idle in transaction'; rollback;
┌──────┬───────────────────┐
│ pid │ application_name │
├──────┼───────────────────┤
│ 2350 │ worker1 on job 31 │
└──────┴───────────────────┘
(1 row)
PostgreSQL users with admin rights can terminate sessions
The postgres user is one such user:
Terminal 3 postgres@[local]:5432/postgres# select pg_terminate_backend(2350); commit; ┌──────────────────────┐ │ pg_terminate_backend │ ├──────────────────────┤ │ t │ └──────────────────────┘ (1 row)
Meanwhile, back in terminal 2:
postgres@[local]:5432/postgres# update t set name = 'zzz' where id = 1; UPDATE 1 postgres@[local]:5432/postgres*#
Our update completed and we got our prompt back. We are also now in an uncommitted transaction.
Back to terminal 3:
Worker 1's connection is gone
Worker 2 is idle in transaction because he has not committed yet
Terminal 3
postgres@[local]:5432/postgres# select pg_terminate_backend(2350); commit;
┌──────────────────────┐
│ pg_terminate_backend │
├──────────────────────┤
│ t │
└──────────────────────┘
(1 row)
Meanwhile, back in terminal 2:
postgres@[local]:5432/postgres# update t set name = 'zzz' where id = 1;
UPDATE 1
postgres@[local]:5432/postgres*#
Our update completed and we got our prompt back. We are also now in an uncommitted transaction.
Back to terminal 3:
Worker 1's connection is gone
Worker 2 is idle in transaction because he has not committed yet
postgres@[local]:5432/postgres#
select application_name,
state,
waiting,
query
from pg_stat_activity; rollback;
┌───────────────────┬─────────────────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ application_name │ state │ waiting │ query │
├───────────────────┼─────────────────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│ worker3 on job 33 │ active │ f │ select application_name, state, waiting, query from pg_stat_activity; │
│ worker2 on job 32 │ idle in transaction │ f │ update t set name = 'zzz' where id = 1; │
└───────────────────┴─────────────────────┴─────────┴───────────────────────────────────────────────────────────────────────┘
(2 rows)
Let's commit terminal 2's transaction
Terminal 2
postgres@[local]:5432/postgres*# commit;
COMMIT
What's going on with psql in terminal 1?
Seems OK. Let's try to commit.
postgres@[local]:5432/postgres*# commit;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres@[local]:5432/postgres#
pg_terminate_backend worked as advertised
pg_stat_activity can also show us the times of various events for any session.
Let's return to terminal 2:
postgres@[local]:5432/postgres# update t set name = 'yyz' where id = 1;
UPDATE 1
postgres@[local]:5432/postgres*# <-- open transaction
Terminal 3
postgres@[local]:5432/postgres# \x
Expanded display is on.
postgres@[local]:5432/postgres#
select backend_start,
xact_start,
query_start,
state_change,
state,
query
from pg_stat_activity
where application_name like 'worker2%'; rollback;
┌─[ RECORD 1 ]──┬─────────────────────────────────────────┐
│ backend_start │ 2015-10-12 12:46:22.735595-04 │
│ xact_start │ 2015-10-12 13:52:40.471049-04 │
│ query_start │ 2015-10-12 13:52:40.47122-04 │
│ state_change │ 2015-10-12 13:52:40.47169-04 │
│ state │ idle in transaction │
│ query │ update t set name = 'yyz' where id = 1; │
└───────────────┴─────────────────────────────────────────┘
Terminal 2's session (backend_start) started a while ago, but our current query started more recently, ran very quickly, and then become idle in transacton (query_start versus state_change).
Terminal 2, open transaction
postgres@[local]:5432/postgres*# insert into t (id, name) values (2, 'yhz');
INSERT 0 1
postgres@[local]:5432/postgres*# <-- transaction still open
Terminal 3
postgres@[local]:5432/postgres#
select backend_start,
xact_start,
query_start,
state_change,
state,
query
from pg_stat_activity
where application_name like 'worker2%'; rollback;
┌─[ RECORD 1 ]──┬─────────────────────────────────────────────┐
│ backend_start │ 2015-10-12 12:46:22.735595-04 │
│ xact_start │ 2015-10-12 13:52:40.471049-04 │
│ query_start │ 2015-10-12 14:01:10.644656-04 │
│ state_change │ 2015-10-12 14:01:10.645041-04 │
│ state │ idle in transaction │
│ query │ insert into t (id, name) values (2, 'yhz'); │
└───────────────┴─────────────────────────────────────────────┘
Terminal 2's query_start is different from its transaction_start because query_start is now for terminal 2's second query. But again, state_change shows we transitioned very quickly to idle in transaction.
Terminal 2
postgres@[local]:5432/postgres*# commit;
COMMIT
postgres@[local]:5432/postgres# <-- no longer in transaction
Terminal 3
postgres@[local]:5432/postgres#
select backend_start,
xact_start,
query_start,
state_change,
state,
query
from pg_stat_activity
where application_name like 'worker2%'; rollback;
┌─[ RECORD 1 ]──┬───────────────────────────────┐
│ backend_start │ 2015-10-12 12:46:22.735595-04 │
│ xact_start │ [NULL] │
│ query_start │ 2015-10-12 14:05:20.204116-04 │
│ state_change │ 2015-10-12 14:05:20.209775-04 │
│ state │ idle │
│ query │ commit; │
└───────────────┴───────────────────────────────┘
Terminal 2 is no longer in a transaction (xact_start is null) and is idle. Note that idle is a perfectly fine state for a session to be in, unless you are worried about connections to your database never being cleaned up.
A final fun set command
Enforce the amount of time any single query is supposed to take, on a per-connection basis:
Terminal 2 postgres@[local]:5432/postgres# set statement_timeout to 1000; SET postgres@[local]:5432/postgres*# commit; COMMIT postgres@[local]:5432/postgres# select pg_sleep(2); ERROR: 57014: canceling statement due to statement timeout postgres@[local]:5432/postgres!# rollback; ROLLBACK
Comments
Post a Comment