CODEWITHSUNDEEP
postgresqlindexingunique-index
Alex
Alex

Reputation: 15708

Did this idle query cause my create unique index command to lock up?

I had an open connection from Matlab to my postgres server, and the last query was insert into table_name ..., which had state idle when I look at the processes on the database server using:

SELECT datname,pid,state,query FROM pg_stat_activity;

I tried to create a unique index on table_name and it was taking a very long time, with no discernable CPU usage for the pgadmin process. When I closed the connection from Matlab, the query dropped out of pg_stat_activity, and the unique index was immediately built.

Was the idle query the reason why it took so long to build the index?

Upvotes: 0

Views: 502

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246393

No, a session in state “idle” does not hold any locks and cannot block anything. It's “idle in transaction” sessions that usually are the problem, because they tend to hold locks over a long time. Such sessions are almost always caused by an application bug.

To investigate problems like that, look at the view pg_locks. A hanging CREATE INDEX statement will usually hang acquiring the ACCESS EXCLUSIVE lock on the table to be indexed. You should see that by a value of FALSE in the granted column of pg_locks. Then figure out which backend (pid) has a lock on the table in question, an you have the culprit(s).

Upvotes: 2

Related Questions