Having trouble creating unique index on a table. Statement fails with following message
>CREATE UNIQUE INDEX CONCURRENTLY my_table_pkey_new ON my_table (new_id);
ERROR: canceling statement due to lock timeout
It is not clear why I get lock timeout. Concurrent index creation shouldn't lock the table.
I also tried to increase lock timeout but without success
test=> show lock_timeout;
lock_timeout
--------------
5min
(1 row)
test=> set lock_timeout to 99999999;
SET
test=> show lock_timeout;
lock_timeout
--------------
5min
(1 row)
Concurrent index creation shouldn't lock the table.
Of course it locks the table. What should happen if someone tries to drop the table while it was being indexed? It just locks it in a mode which does not conflict with INSERT, UPDATE, or DELETE. But it does conflict with other operations, including VACUUM and (ironically, perhaps) other CREATE INDEX CONCURRENTLY. It also takes a stronger lock which conflicts with everything, but it only holds that lock momentarily. But if that lock is not immediately available, it can timeout while waiting for it.
test=> show lock_timeout;
lock_timeout
--------------
5min
(1 row)
test=> set lock_timeout to 99999999;
SET
test=> show lock_timeout;
lock_timeout
--------------
5min
(1 row)
Is all of this run in one session, with no intervening statements, as the contiguous code indentation would suggest? If so, then I think you must be running some modified version of PostgreSQL, as I don't think any version of the community PostgreSQL behaves that way.