Unique constraint error instead of serialization_failure
От | Benny Kramek |
---|---|
Тема | Unique constraint error instead of serialization_failure |
Дата | |
Msg-id | CAGPCyEZG76zjv7S31v_xPeLNRuzj-m=Y2GOY7PEzu7vhB=yQog@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Unique constraint error instead of serialization_failure
Re: Unique constraint error instead of serialization_failure |
Список | pgsql-bugs |
Tested PostgreSQL versions: 10.7, 12.2 Hello, I have found an example where I expect to get a serialization failure error, but instead receive a unique constraint error. My understanding is that this is not supposed to happen starting with PostgreSQL version 9.6 because of this patch: <https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fcff8a575198478023ada8a48e13b50f70054766> The example has a table where each "item_id" has a single version, and we want to increment the version of one of the items. Here is the full session that demonstrates the error. Notice at the very end we get a "unique constraint" error. If we run the example from the beginning, this time without the "UNIQUE (item_id, version)" constraint, then at the end we get a serialization failure as expected. -- -- Setup: -- CREATE TABLE t ( item_id INT NOT NULL, version INT NOT NULL, created_at TIMESTAMPTZ NOT NULL, UNIQUE (item_id, version), UNIQUE (item_id, created_at) ); INSERT INTO t (item_id, version, created_at) VALUES (10, 1, now() - INTERVAL '2 SECOND'), (10, 2, now() - INTERVAL '1 SECOND'); -- | -- Transaction 1 -- | -- Transaction 2 -- -- | START TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT version FROM t WHERE NOT EXISTS( SELECT 1 FROM t t2 WHERE t.item_id = t2.item_id AND t.created_at < t2.created_at) AND item_id = 10; -- Result: "2" SELECT version FROM t WHERE NOT EXISTS( SELECT 1 FROM t t2 WHERE t.item_id = t2.item_id AND t.created_at < t2.created_at) AND item_id = 10; -- Result: "2" -- Insert next value: 2 + 1: INSERT INTO t (item_id, version, created_at) VALUES (10, 3, now()); COMMIT; -- Insert next value: 2 + 1: INSERT INTO t (item_id, version, created_at) VALUES (10, 3, now()); -- ERROR: duplicate key value violates -- unique constraint -- "t_item_id_version_key" -- DETAIL: Key -- (item_id, version)=(10, 3) -- already exists.
В списке pgsql-bugs по дате отправления: