11

I'm trying to create an index concurrently.

CREATE INDEX CONCURRENTLY blocks_expr_idx6 ON blocks USING btree ('blah') WHERE "type" = 'user root';

12 hours after I ran the query, if I run \d blocks Postgres reports that the index is INVALID:

"blocks_expr_idx6" btree ('blah') WHERE "type" = 'user root' INVALID

The documentation says:

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID.

The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is to rebuild the index with REINDEX. However, since REINDEX does not support concurrent builds, this option is unlikely to seem attractive.)

How can I figure out why Postgres marked the index as INVALID?

Improve this question
1
  • 4
    Checking logs can say something. deadlocks are logged, any other errors are logged too.. Do you have any other invalid indexes ? SELECT * FROM pg_index i WHERE i.indisvalid IS FALSE;
    – Sahap Asci
    Dec 16, 2016 at 22:50

1 Answer 1

Reset to default
4

One simple answer to this is to drop it, run it again, keep the terminal window around (or otherwise log the output) and see what error happens.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.