From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: rbiro@interfacefinancial.com
Subject: Index Locking Order is important
Date: Mon, 21 Sep 2020 12:52:21 +0000
Message-ID: <160069274124.14061.4149200726948746846@wrigleys.postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, rbiro@interfacefinancial.com
Subject: Re: Index Locking Order is important
In-Reply-To: <160069274124.14061.4149200726948746846@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox