CODEWITHSUNDEEP
sqlpostgresqltransactionsddlpg-stat-statements
modulitos
modulitos

Reputation: 15814

Why is my "CREATE INDEX CONCURRENTLY .." command blocked by a "SELECT FROM" query? (Postgres 9.6)

I was running a migration to create an index, but the migration was blocked by another query. I resolved the problem after discovering that there was another query blocking my migrations; and after cancelling the blocking query, I was able to successfully run the migration.

(My server is using Postgres 9.6 on Linux)

Here is how I discovered that my migration was blocked:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

which returned the following result:

| pid | usename | query                                                                           | blocking_id | blocking_query                                                              |
|-----+---------+---------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------|
| 123 | my_user | CREATE  INDEX CONCURRENTLY "idx_orders_on_widget_id" ON "orders"  ("widget_id") | 456         | SELECT  "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2 |

How is it possible that my migration (the CREATE INDEX CONCURRENTLY .. query) was being blocked by the SELECT .. FROM .. query? Even if the process running the blocking query was in a zombie state, I don't understand how my index creation query can be blocked by a "SELECT .. FROM .." query.

Can anyone offer insights about how this is possible?

If it helps, here is my schema (simplified for this question):

Orders

id

widget_id

customer_id (FK to Customers)

Customers

id

company_id (FK to Companies)

Companies

id

Some additional notes:

Upvotes: 0

Views: 2990

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246383

The documentation says:

PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate.

Upvotes: 2

Related Questions