For a long time there was no real wait interface in PostgreSQL core. Now, with the upcoming 9.6, pg_stat_activity was extended to provide more meaningful information for what a session is currently waiting on. If you are interested in reading through the history: It all started here. Lets have a look at it by doing same simple tests.
When I want to see what a session is waiting for I’ll at least need two sessions: One doing some work and the other for querying the wait event information. Obviously I’ll need to know my backend process identifier which I then can use to query pg_stat_activity. PostgreSQL provides a function to get the backend process id:
(postgres@[local]:5432) [postgres] > select * from pg_backend_pid(); pg_backend_pid ---------------- 3048 (1 row)
Using this session lets create a table and populate it with some rows:
(postgres@[local]:5432) [postgres] > create table t1 (a int); CREATE TABLE (postgres@[local]:5432) [postgres] > insert into t1 (a) values ( generate_series(1,1000000) ); INSERT 0 1000000
If you are fast enough to switch to the other session and query for the wait event you’ll see this:
(postgres@[local]:5432) [postgres] > select wait_event_type,wait_event from pg_stat_activity where pid = 3048; wait_event_type | wait_event -----------------+-------------- LWLockNamed | WALWriteLock (1 row)
So, what does this mean? The documentation gives the answer:
- LWLockNamed: The backend is waiting for a specific named lightweight lock. Each such lock protects a particular data structure in shared memory. wait_event will contain the name of the lightweight lock.
- WALWriteLock: Waiting for WAL buffers to be written to disk.
Makes sense: Changed data needs to be written to the WAL.
Another example: In one session I’ll start a transaction and update one row in the table without closing/committing the same:
(postgres@[local]:5432) [postgres] > begin; BEGIN Time: 0.103 ms (postgres@[local]:5432) [postgres] > update t1 set a = 2 where a = 1; UPDATE 1 Time: 56.086 ms (postgres@[local]:5432) [postgres] >
In a third session lets try to update the same row:
(postgres@[local]:5432) [postgres] > select * from pg_backend_pid(); pg_backend_pid ---------------- 3397 (1 row) Time: 0.220 ms (postgres@[local]:5432) [postgres] > update t1 set a = 2 where a = 1;
This should cause a wait event, but which one?
(postgres@[local]:5432) [postgres] > select wait_event_type,wait_event from pg_stat_activity where pid = 3397; wait_event_type | wait_event -----------------+--------------- Lock | transactionid (1 row)
Another event type and another event:
- Lock: The backend is waiting for a heavyweight lock. Heavyweight locks, also known as lock manager locks or simply locks, primarily protect SQL-visible objects such as tables. However, they are also used to ensure mutual exclusion for certain internal operations such as relation extension. wait_event will identify the type of lock awaited.
- transactionid: Waiting for a transaction to finish
In PostgreSQL DDL is transactional, too, so we should be able to create another wait event when we try to modify the table in one session:
(postgres@[local]:5432) [postgres] > begin; BEGIN Time: 0.085 ms (postgres@[local]:5432) [postgres] > alter table t1 add column b int; ALTER TABLE Time: 0.318 ms (postgres@[local]:5432) [postgres] >
… and then try to update a row in another session:
(postgres@[local]:5432) [postgres] > update t1 set a = 2 where a = 1;
What is reported?
(postgres@[local]:5432) [postgres] > select wait_event_type,wait_event from pg_stat_activity where pid = 3397; wait_event_type | wait_event -----------------+------------ Lock | relation (1 row)
- Lock: The backend is waiting for a heavyweight lock. Heavyweight locks, also known as lock manager locks or simply locks, primarily protect SQL-visible objects such as tables. However, they are also used to ensure mutual exclusion for certain internal operations such as relation extension. wait_event will identify the type of lock awaited.
- relation: Waiting to acquire a lock on a relation.
Currently there are 68 documented wait events and I am sure more will show up during the next releases. This is a huge help if you need to troubleshoot sessions because now you do not need extensions any more and do not need to compile PostgreSQL with dynamic tracing.