serializable anomaly - duplicate primary keys
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253222psql -h localhost -U postgresBuilt from patchset v2 (message #2), September 15, 2026 at 12:44 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253222_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253222_2 && git checkout t253222_2Patchset v2 (message #2) is on t253222_2
Hi,
I found a SERIALIZABLE anomaly where a transaction reads a row, successfully
inserts another row with the same primary key, observes both rows, and
commits.
Setup:
CREATE TABLE uq (
k int PRIMARY KEY,
j int,
v int
);
INSERT INTO uq VALUES (1, 1000000, 0);
INSERT INTO uq SELECT g, g, 0 FROM generate_series(100, 2000) g;
CREATE INDEX uq_j ON uq(j);
VACUUM ANALYZE uq;
The filler rows are needed so that the planner picks index scans, and so
that
j = 1000000 and j = 2 fall on different leaf pages of uq_j.
Session 1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM uq WHERE k = 1;
-- 1 | 1000000 | 0
Session 2:
BEGIN ISOLATION LEVEL SERIALIZABLE;
DELETE FROM uq WHERE j = 1000000;
COMMIT;
Back in session 1:
INSERT INTO uq VALUES (1, 2, 1);
SELECT * FROM uq WHERE k = 1 ORDER BY j;
Result:
k | j | v
---+---------+---
1 | 2 | 1
1 | 1000000 | 0
Session 1 can then commit successfully.
There is no serial order for this result. Session 1 saw the original row,
but
its INSERT was also allowed to rely on the other transaction's committed
deletion.
As a control, changing session 2 to:
DELETE FROM uq WHERE k = 1;
causes a serialization failure. The outcome therefore depends on whether the
deletion uses the primary-key index or the secondary index.
The closest prior work is fcff8a57519, which added SSI conflict checking
before
reporting unique violations and noted that constraint reads do not fully
participate in SSI. Here no unique violation is reported, and the missing
dependency allows both transactions to commit.
I found no current thread discussing this SnapshotDirty uniqueness-check
case.
The current SxactGlobalXmin SSI patch is unrelated.
Best,
Jacob Brazeal
On 28 Jul 2026, at 10:36, Jacob Brazeal <jacob.brazeal@gmail.com> wrote:
I found a SERIALIZABLE anomaly where a transaction reads a row, successfully
inserts another row with the same primary key, observes both rows, and commits.
Hi Jacob,
Thanks for the report. I reproduced it, including with INSERT ... ON
CONFLICT DO NOTHING.
This is related to the ON CONFLICT issue discussed in [0]/messages/by-id/165342c0-0c75-461e-b334-b997639ad48d@aphyr.com, but has the
opposite ordering: here the deletion commits first, and the unique check
relies on it through SnapshotDirty.
The window seems fairly narrow: a serializable transaction must read a row,
keep its snapshot across another transaction's deletion, and then reuse the
same key. It does not corrupt the index or leave a persistent duplicate, but
the committed transaction violates the SERIALIZABLE guarantee. This seems
plausible in delete-and-recreate or upsert workflows with long transactions.
PFA invasive fix on top of HEAD. The table AM reports the deleting XID when
SnapshotDirty skips a tuple still visible to the transaction snapshot. SSI
then fails only if there is already an rw-conflict to that transaction. It
also marks the transaction doomed before raising the error, so a savepoint
cannot hide the failure.
This extends the table AM interface. An ABI-preserving back-branch fix can
be considered separately.
Best regards, Andrey Borodin.
[0]: /messages/by-id/165342c0-0c75-461e-b334-b997639ad48d@aphyr.com
On 8/7/2026 8:59 AM, Andrey Borodin wrote:
On 28 Jul 2026, at 10:36, Jacob Brazeal <jacob.brazeal@gmail.com> wrote:
I found a SERIALIZABLE anomaly where a transaction reads a row, successfully
inserts another row with the same primary key, observes both rows, and commits.Hi Jacob,
Thanks for the report. I reproduced it, including with INSERT ... ON
CONFLICT DO NOTHING.This is related to the ON CONFLICT issue discussed in [0], but has the
opposite ordering: here the deletion commits first, and the unique check
relies on it through SnapshotDirty.The window seems fairly narrow: a serializable transaction must read a row,
keep its snapshot across another transaction's deletion, and then reuse the
same key. It does not corrupt the index or leave a persistent duplicate, but
the committed transaction violates the SERIALIZABLE guarantee. This seems
plausible in delete-and-recreate or upsert workflows with long transactions.PFA invasive fix on top of HEAD. The table AM reports the deleting XID when
SnapshotDirty skips a tuple still visible to the transaction snapshot. SSI
then fails only if there is already an rw-conflict to that transaction. It
also marks the transaction doomed before raising the error, so a savepoint
cannot hide the failure.This extends the table AM interface. An ABI-preserving back-branch fix can
be considered separately.Best regards, Andrey Borodin.
[0] /messages/by-id/165342c0-0c75-461e-b334-b997639ad48d@aphyr.com
Andrey,
I am doing security audits of commitfest items. The nbtree part looks
correct. Several non-security related observations though.
The crosscheck isn't gated on the relation.
s1: BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT * FROM t WHERE k=1;
s2: BEGIN ISOLATION LEVEL SERIALIZABLE; UPDATE t SET ... WHERE k=1;
DROP TABLE foo; COMMIT;
s1: CREATE TABLE foo (...); [errors]
That commits on master. Catalog dependencies don't participate in SSI,
so this rejects valid DDL. It wants the same
PredicateLockingNeededForRelation gate the other conflict-out paths have.
The UNIQUE_CHECK_EXISTING skip drops deferred constraints. With the
delete still in progress at insert time the partial check defers, and
the recheck comes back as existing, which the gate skips:
s1: BEGIN SERIALIZABLE; SELECT * FROM test WHERE k=1;
s2: BEGIN SERIALIZABLE; DELETE FROM test WHERE j=1000000;
s1: INSERT INTO test VALUES (1,2);
s2: COMMIT;
s1: COMMIT; [commits, no error]
Immediate constraints wait on the delete and re-check, so they catch it.
Same function misses again once the deleter's been summarized. The
hash_search comes back NULL after SummarizeOldestCommittedSxact evicts
it, and it returns without failing, so the history that errors above
will commit once enough serializable writes have forced summarization.
Exclusion constraints have the same reuse-a-committed-delete hole, and
this doesn't reach them. check_exclusion_or_unique_constraint runs its
own DirtySnapshot scan and never goes through _bt_check_unique. An
EXCLUDE USING btree (k WITH =) version of perm 1 reproduces. You didn't
break it, fcff8a57519 was nbtree-only too, but the root cause in the
commit message covers it, so worth calling in-scope or follow-up.
One small thing, table_index_fetch_tuple_check calls the new AM callback
with no NULL check, so an out-of-tree AM compiled against the new
tableam.h crashes on its first unique check instead of tripping an
assertion.
--
Bryan Green
EDB: https://www.enterprisedb.com