How do I find out why CREATE INDEX CONCURRENTLY is failing?

Question

I set off the creation of an index with the command

CREATE INDEX CONCURRENTLY index_Name ON namespace.table_name(column_name);

and watched the action using this command on another session:

select now()::time(0), a.query, p.phase, p.blocks_total, p.blocks_done, p.tuples_total, p.tuples_done 
from pg_stat_progress_create_index p 
  join pg_stat_activity a on p.pid = a.pid 
\watch 2

After a while, the index creation stopped as seen in these two subsequent watch calls. The first one shows progress, and the subsequent one is empty. This indicates that the index build shut down between these consecutive 4-minute calls.

now	query	phase	blocks_total	blocks_done	tuples_total	tuples_done
21:17:26	CREATE INDEX CONCURRENTLY any_transfers_to_address_idx ON ethereum.any_transfers(to_address);	building index: scanning table	85807113	30279326	0	0

now	query	phase	blocks_total	blocks_done	tuples_total	tuples_done

Is there a way I can find out exactly why this would happen? Are there logs I can query? Any and all suggestions welcome, thanks.

Answer 1

Look into the database log. If you left the default configuration, you will see a log entry if CREATE INDEX was terminated by an error (for example, through statement_timeout). If it failed, remove the INVALID index on the table that is left behind in that case.