A68: Skipping analyze - lock not available

Category: Autovacuum Events
SQLSTATE: 55P03 (Class 55 - Object Not In Prerequisite State: lock_not_available)
Urgency: low

Example Postgres Log Output:

LOG: skipping analyze of "pgbench_tellers" --- lock not available


This event indicates that an ANALYZE operation couldn't start because it couldn't acquire the required lock, an ShareUpdateExclusiveLock, on the table.

Recommended Action:

Review all current lock holders on the table by running the following:

SELECT * FROM pg_locks WHERE relation = 'mytable'::regclass;

This will return something like this:

 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |        mode         | granted | fastpath
 relation |  1691898 |  1693426 |      |       |            |               |         |       |          | 3/22886            | 3502 | AccessExclusiveLock | t       | f
(1 row)

In this case you can see that there is a granted AccessExclusiveLock that prevents ANALYZE from running.

The locks that are relevant and can block ANALYZE:

  • ShareUpdateExclusiveLock
  • ShareLock
  • ShareRowExclusiveLock
  • ExclusiveLock
  • AccessExclusiveLock

You will need to wait for the lock to be available, or cancel the operation running that holds the lock (see pid field) for ANALYZE to run.

Learn More:

No additional resources available.

Download Free eBook: The Top 6 Postgres Log Events
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →