The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/index-unique-checks.html Description:
When there are multiple unique indexes on a table, at least one that allows NULL, the order of the locks are important. This should be explained in the documentation. Consider two tables: create table foo(id1 bigint primary key, id2 bigint unique); and create table bar (id1 bigint not null, id2 bigint unique); With indexes create in this order: create unique index bar_id2 on bar(id2); create unique index zbar_id1 on bar(id1); alter table bar add primary key using index zbar_id1; One might expect foo and bar to behave in identical ways. However, if one opens two connection and does BEGIN; insert into bar(id1) values (1); -- Do not commit, switch to second connection BEGIN; insert into bar(id1, id2) values (1, 2); -- This blocks as it should -- switch back to the first connection update bar set id2=2 where id1=1; commit; One of the processes should get something like ERROR: deadlock detected DETAIL: Process 6888 waits for ShareLock on transaction 142875965; blocked by process 6897. Process 6897 waits for ShareLock on transaction 142875736; blocked by process 6888. HINT: See server log for query details. CONTEXT: while inserting index tuple (0,3) in relation "bar_id2_key" If one does the same thing on foo, process 2 will get ERROR: duplicate key value violates unique constraint "foo_pkey" DETAIL: Key (id1)=(1) already exists. Which is what I would expect from both tables. At a minimum, index locking order should be documented and explained. It may also be desirable to have a configuration option to switch the index locking order to alphabetical by name to make it easy to control like triggers.