SSI: A patch for a Serializability violation
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:t253607psql -h localhost -U postgresBuilt from patchset v4 (message #4), September 20, 2026 at 03:11 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 t253607_4 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 t253607_4 && git checkout t253607_4Patchset v4 (message #4) is on t253607_4
Hi,
While looking into the SSI implementation in Postgres, I came across a
serializability violation. The attached TAP test in the patch demonstrates
this behavior: without the fix, a transaction that violates serializability
runs without aborting, whereas with the fix, one of the transactions in the
cycle is correctly aborted.
The problem occurs when a serializable transaction is summarized.
CheckTargetForConflictsIn() is called when a tuple's writer checks if
another transaction has taken predicate locks on it. If the transaction
taking the predicate locks is not summarized, there is no issue. However,
if it is summarized, the check references sxact->finishedBefore, which is
never populated for OldCommittedSxact.
Additionally, when predicate locks are summarized, they are not easily
released if certain long-running transactions keep the writable transaction
count greater than zero. The provided script
(ssi_lock_reclamation_repro.sh) uses pgbench to demonstrate that one can
easily run out of shared memory space for predicate locks in the presence
of these transactions.
To solve both problems, I propose adding a finishedBefore field to
predicate locks. This field will be populated when the transaction is
summarized. It can then be used in CheckTargetForConflictsIn() to determine
if the reader overlaps with the writer, resolving the serializability
violation. Furthermore, this field can be used to free up the predicate
lock when SxactGlobalXmin crosses finishedBefore.
Regards,
Vaijayanti Bharadwaj
Hi Vaijayanti,
Jacob reported what looks like the same bug earlier [0]/messages/by-id/CA+COZaCtK=UQbeQwdAoRw27J+58bJBC+yNHP4OH2+y_t2UtFAg@mail.gmail.com, and I sent a
tentative commitSeqNo-based fix in that thread.
Your patch made me notice a case which we did not consider there: imported
serializable snapshots. lastCommitBeforeSnapshot is recorded when the
snapshot is imported, while finishedBefore is compared with the imported
snapshot's xmin. The two approaches can therefore disagree about a reader
which commits between export and import. Your patch preserves the existing
non-summarized behavior in this case; mine does not.
Was this case part of your reasoning? It seems worth writing explicitly the
expected behavior here.
Thank you!
Best regards, Andrey Borodin.
[0]: /messages/by-id/CA+COZaCtK=UQbeQwdAoRw27J+58bJBC+yNHP4OH2+y_t2UtFAg@mail.gmail.com
My fix came from a different direction. I first hit shared memory
exhaustion from summarized predicate locks never being freed, and while
fixing that I considered adding finishedBefore to PREDICATELOCK. Once I had
the field, I noticed the check in CheckTargetForConflictsIn() can use
finishedBefore which is not populated for OldCommittedSxact, so I reused it
for both.
But you have a very good point about imported snapshots.
GetSerializableTransactionSnapshotInt() sets SeqNo.lastCommitBeforeSnapshot
from the live PredXact->LastSxactCommitSeqNo at import time, while
sxact->xmin comes from the imported snapshot, and nothing reconciles the
two. lastCommitBeforeSnapshot can end up too large. Like you said, a reader
that commits between export and import loses its rw-edge. That is a missed
conflict rather than a false positive.
I think its a super rare case, it needs a read-write SERIALIZABLE
transaction importing a snapshot, pg_dump uses REPEATABLE READ, READ ONLY
for any connection that imports, and logical replication tablesync is also
REPEATABLE READ. But an application can do it.
I think your approach would work for the above case with a small addition:
flag sxacts that imported a snapshot, and in
PredicateLockIsForOverlappingTransaction() treat every summarized lock as
overlapping for those. It is pessimistic, but only for a rare case.
So far, I have only looked into PredicateLockIsForOverlappingTransaction(),
and have yet to evaluate other uses of lastCommitBeforeSnapshot for the
case of importing a snapshot. But if you have done so, I would like to
understand that.
Thank you!
Vaijayanti Bharadwaj
On Mon, Aug 31, 2026 at 1:37 PM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
Show quoted text
Hi Vaijayanti,
Jacob reported what looks like the same bug earlier [0], and I sent a
tentative commitSeqNo-based fix in that thread.Your patch made me notice a case which we did not consider there: imported
serializable snapshots. lastCommitBeforeSnapshot is recorded when the
snapshot is imported, while finishedBefore is compared with the imported
snapshot's xmin. The two approaches can therefore disagree about a reader
which commits between export and import. Your patch preserves the existing
non-summarized behavior in this case; mine does not.Was this case part of your reasoning? It seems worth writing explicitly
the
expected behavior here.Thank you!
Best regards, Andrey Borodin.
[0]
/messages/by-id/CA+COZaCtK=UQbeQwdAoRw27J+58bJBC+yNHP4OH2+y_t2UtFAg@mail.gmail.com
Hi Andrey,
After looking into this further, I realized that to solve the issue you
raised regarding imported snapshots, we can look for the actual sxact that
exported the snapshot. We can then use its lastCommitBeforeSnapshot as the
importer's lastCommitBeforeSnapshot, provided the importing transaction is
read-write.
If the importer is read-only, a higher value does not matter and would only
result in false positives or delayed resource reclamation. For a read-write
importer, using the exporter's value vs. PredXact->LastSxactCommitSeqNo
only matters with your patch to fix the consistency issue, as it could
otherwise cause missed conflicts.
In other words, combining the attached patch with yours resolves the
inconsistency. Without your patch, the attached patch makes no difference.
Please let me know if you agree. I have attached the patch to illustrate
this approach. I am still testing this specific scenario and will share the
tests in a subsequent patch.
Regards,
Vaijayanti Bharadwaj
On Mon, Aug 31, 2026 at 3:35 PM Vaijayanti Bharadwaj <
vaijayanti.bharadwaj@enterprisedb.com> wrote:
Show quoted text
My fix came from a different direction. I first hit shared memory
exhaustion from summarized predicate locks never being freed, and while
fixing that I considered adding finishedBefore to PREDICATELOCK. Once I had
the field, I noticed the check in CheckTargetForConflictsIn() can use
finishedBefore which is not populated for OldCommittedSxact, so I reused it
for both.But you have a very good point about imported snapshots.
GetSerializableTransactionSnapshotInt() sets SeqNo.lastCommitBeforeSnapshot
from the live PredXact->LastSxactCommitSeqNo at import time, while
sxact->xmin comes from the imported snapshot, and nothing reconciles the
two. lastCommitBeforeSnapshot can end up too large. Like you said, a reader
that commits between export and import loses its rw-edge. That is a missed
conflict rather than a false positive.I think its a super rare case, it needs a read-write SERIALIZABLE
transaction importing a snapshot, pg_dump uses REPEATABLE READ, READ ONLY
for any connection that imports, and logical replication tablesync is also
REPEATABLE READ. But an application can do it.I think your approach would work for the above case with a small addition:
flag sxacts that imported a snapshot, and in
PredicateLockIsForOverlappingTransaction() treat every summarized lock as
overlapping for those. It is pessimistic, but only for a rare case.So far, I have only looked into
PredicateLockIsForOverlappingTransaction(), and have yet to evaluate other
uses of lastCommitBeforeSnapshot for the case of importing a snapshot. But
if you have done so, I would like to understand that.Thank you!
Vaijayanti BharadwajOn Mon, Aug 31, 2026 at 1:37 PM Andrey Borodin <x4mmm@yandex-team.ru>
wrote:Hi Vaijayanti,
Jacob reported what looks like the same bug earlier [0], and I sent a
tentative commitSeqNo-based fix in that thread.Your patch made me notice a case which we did not consider there: imported
serializable snapshots. lastCommitBeforeSnapshot is recorded when the
snapshot is imported, while finishedBefore is compared with the imported
snapshot's xmin. The two approaches can therefore disagree about a reader
which commits between export and import. Your patch preserves the
existing
non-summarized behavior in this case; mine does not.Was this case part of your reasoning? It seems worth writing explicitly
the
expected behavior here.Thank you!
Best regards, Andrey Borodin.
[0]
/messages/by-id/CA+COZaCtK=UQbeQwdAoRw27J+58bJBC+yNHP4OH2+y_t2UtFAg@mail.gmail.com