Postgres lock_timeout when CREATE UNIQUE INDEX CONCURRENTLY

Question

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)

What is your PostgreSQL version ? Do you have idle transactions started **before** index creation (because *PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate.*) ? — pifor, Jun 16 '20 at 14:12
probably this would answer my question. https://stackoverflow.com/questions/26489244/how-to-detect-query-which-holds-the-lock-in-postgres . But by the time I fount it, the lock has gone and index was created. — Rugnar, Jun 16 '20 at 15:36

jjanes · Answer 1 · 2020-06-16T15:26:00.510

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.

> modified version of PostgreSQL This can be the case I think. — Rugnar, Jun 16 '20 at 15:38

Postgres lock_timeout when CREATE UNIQUE INDEX CONCURRENTLY

1 Answers1