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 v7 (message #7), October 23, 2026 at 03:24 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_7 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_7 && git checkout t253222_7Patchset v7 (message #7) is on t253222_7
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
Hi,
This extends the table AM interface. An ABI-preserving back-branch fix can
be considered separately.
Actually I think we should do just the opposite - focus on the patch
that can be backported and discuss possible improvements for the
`master` branch later.
The root cause is that unique checks use a dirty snapshot. They can
decide a key is unused because of a deletion the transaction cannot
see. The fix might be to ask our own snapshot the same question, and
fail if it still sees the key in use. I don't think this will require
an ABI change.
--
Best regards,
Aleksander Alekseev
Hi,
The root cause is that unique checks use a dirty snapshot. They can
decide a key is unused because of a deletion the transaction cannot
see. The fix might be to ask our own snapshot the same question, and
fail if it still sees the key in use. I don't think this will require
an ABI change.
I prepared a PoC patch that shows how this could be archived. Also it
includes regression tests for everything discussed above.
One drawback I discovered is that the patch introduces a
false-positive conflict detection that causes the existing
partition-key-update-3.spec to fail. I'm not 100% certain what to do
about it. Given the fact that SSI generally has false-positives, maybe
we should just rewrite the test? This seems to be better compared to
what we currently have and personally I don't have better proposals at
the moment.
Thoughts?
--
Best regards,
Aleksander Alekseev
Hi,
Actually I think we should do just the opposite - focus on the patch
that can be backported and discuss possible improvements for the
`master` branch later.
I prepared a PoC patch that shows how this could be archived. Also it
includes regression tests for everything discussed above.One drawback I discovered is that the patch introduces a
false-positive conflict detection that causes the existing
partition-key-update-3.spec to fail. I'm not 100% certain what to do
about it. Given the fact that SSI generally has false-positives, maybe
we should just rewrite the test? This seems to be better compared to
what we currently have and personally I don't have better proposals at
the moment.
I think I found a fix that doesn't break ABI or the existing
behaviour. It introduces a per-backend global variable which is not
pretty, but it works and we can always refactor the code in the
`master` branch after backpatching.
Please see PoC v2. Any feedback is most welcome.
--
Best regards,
Aleksander Alekseev