From: | Benny Kramek <benny(at)medflyt(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Unique constraint error instead of serialization_failure |
Date: | 2020-05-20 21:24:09 |
Message-ID: | CAGPCyEZG76zjv7S31v_xPeLNRuzj-m=Y2GOY7PEzu7vhB=yQog@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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:
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.
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2020-05-21 00:52:29 | BUG #16454: Mixed isolation levels inside transactions |
Previous Message | Peter Eisentraut | 2020-05-20 14:43:25 | Re: Re : Re: BUG #16441: Cannot multi-insert into generated column with DEFAULT value |