Unique constraint error instead of serialization_failure

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:

<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.

Responses

Browse pgsql-bugs by date

  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