| From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
| Cc: | rbiro(at)interfacefinancial(dot)com |
| Subject: | Index Locking Order is important |
| Date: | 2020-09-21 12:52:21 |
| Message-ID: | 160069274124.14061.4149200726948746846@wrigleys.postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-docs |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Doc comments form | 2020-09-22 11:39:57 | Add support files |
| Previous Message | Tom Lane | 2020-09-20 18:14:00 | Re: Please provide examples of rows from |