how to end the PostgreSQL create index properly

Question

I am using this command to create index in PostgreSQL 13:

CREATE INDEX dict_word_idx ON dict (word); 

but now the sql runs for more than 12hours, I am not sure the sql still working or something. I use this command to show the current running sql:

SELECT 
 procpid, 
 start, 
 now() - start AS lap, 
 current_query 
FROM 
 (SELECT 
 backendid, 
 pg_stat_get_backend_pid(S.backendid) AS procpid, 
 pg_stat_get_backend_activity_start(S.backendid) AS start, 
 pg_stat_get_backend_activity(S.backendid) AS current_query 
 FROM 
 (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
 ) AS S 
WHERE 
 current_query <> '<IDLE>' 
ORDER BY 
 lap DESC;

shows result of create index like this:

1303 2021年10月04日 09:24:27 13:12:49.940239 CREATE INDEX dict_word_idx ON dict (word);

but the pg_stat_progress_create_index shows nothing:

select *
from pg_stat_progress_create_index;

then I tried to use this sql to kill the create index task:

SELECT pg_cancel_backend(1303);

It seems not work, the create index command still stand there. what should I do to figure out what's happening right now. The SQL still busy running and I should waiting? the craeted index command should not be killed like this? what should I do to properly handle this situation and do not make the database failed in the furture.

• Laurenz Albe: Please show the output of SELECT state, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 1303;.

Answer 1

Your query for current activity is incredibly obsolete. Since 9.2, the text of completed queries don't get changed to '<IDLE>', they continue to show up as their original query text. It is there state that gets changed to 'idle'.

So most likely what you are seeing is that the index build has already finished.