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.
