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
– pifor, Commented Jun 16, 2020 at 14:12
probably this would answer my question. stackoverflow.com/questions/26489244/… . But by the time I fount it, the lock has gone and index was created. — Rugnar
– Rugnar, Commented Jun 16, 2020 at 15:36

jjanes · Accepted Answer · 2020-06-16 15:26:00Z

7

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.

edited Jun 16, 2020 at 15:26

answered Jun 16, 2020 at 15:06

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rugnar Over a year ago

> modified version of PostgreSQL This can be the case I think.

Collectives™ on Stack Overflow

Postgres lock_timeout when CREATE UNIQUE INDEX CONCURRENTLY

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related