Postgresql – How to determine why a concurrent Postgres index was marked as INVALID

indexpostgresql

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?

Best Answer

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.