0

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:

  • I discovered that the index was blocked about ~8 hours after I ran the migration.
  • After killing the blocking query, I dropped the idx_orders_on_widget_id index and re-ran the migration to re-create the index.
  • After re-running the migration a second time, the index creation was finished in ~10 mins
  • The server is running on Linux with Postgres 9.6
modulitos
  • 14,737
  • 16
  • 67
  • 110

1 Answers1

2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you for the answer, and referencing the docs :) It explains how a `SELECT .. FROM ..` query that is in a zombie state can block the index creation. But wouldn't the `SELECT .. FROM ..` query have to modify or use that index? The blocking query that I found above is `SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2`, which doesn't seem to modify or use the new index, `widget_id` on the `orders` table. Or maybe I'm missing something? – modulitos Sep 28 '20 at 07:00
  • 1
    It says "could potentially modify". Perhaps the next statement in the same transaction would try to access the index? – Laurenz Albe Sep 28 '20 at 07:02
  • Ah, that's a good point, which explains the behavior. Is it also possible that the *previous* statement in the same transaction already tried accessing the index? Then later on in the transaction, the process enters a zombie state during the `SELECT .. FROM ..` query. If so, would that mean the transaction is using the index? – modulitos Sep 28 '20 at 07:18
  • PostgreSQL doesn't check. It just waits for the concurrent transactions to finish. – Laurenz Albe Sep 28 '20 at 07:29