Skip to content

Commit 6605c0f

Browse files
connortsui20claude
andauthored
Make benchmark ingestion conflict-free and validate before transactions (#10073)
## Summary Replaces #9799. Concurrent benchmark ingest writers can no longer deadlock or queue behind each other, so the conflict retry loop is removed. That loop could never fire anyway: the schema has no foreign keys or triggers, and READ COMMITTED upserts do not raise serialization failures. The only real contention was the `commits` row lock, which each writer held for its whole file. ## Changes - The `commits` row is upserted in its own short transaction, so writers for the same commit do not wait on it. - Fact rows still land all or nothing, in (table, `measurement_id`) order, so overlapping writers cannot deadlock. - Every record is validated before any transaction starts. - `connect_timeout` and `statement_timeout` make a hang fail fast. - Two database-free tests cover validation before BEGIN and the lock order. They run in the existing `Benchmark reporting` job. A fuller suite passed once against PostgreSQL 16 and the website migrations; its concurrent-writer test failed on the previous writer with `LockNotAvailable`. Follow-up in `benchmarks-website`: `CONTRACT.md` says each JSONL file applies in one transaction, which now holds for the fact rows only. 🤖 Generated with [Claude Code](https://claude.com/claude-code) https://claude.ai/code/session_01YSDBXuaTa7DDEj31LYqtp9 --------- Signed-off-by: Connor Tsui <[email protected]> Signed-off-by: "Claude" <[email protected]> Signed-off-by: Claude <[email protected]> Co-authored-by: Claude <[email protected]>
1 parent d8e45e0 commit 6605c0f

3 files changed

Lines changed: 180 additions & 82 deletions

File tree

‎.github/workflows/ci.yml‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -236,10 +236,11 @@ jobs:
236236
- uses: spiraldb/actions/.github/actions/setup-uv@a746510eafaa926484c354541cfc49b2ec06cc63 # 0.18.6
237237
with:
238238
sync: false
239-
- name: Pytest - benchmark reporting
239+
- name: Pytest - benchmark reporting and ingestion
240240
run: |
241241
uv run --no-project --with pytest --with pandas --with numpy --with orjson \
242-
--with tabulate pytest scripts/tests/test_benchmark_reporting.py
242+
--with tabulate --with xxhash \
243+
pytest scripts/tests/test_benchmark_reporting.py scripts/tests/test_post_ingest.py
243244
244245
python-cuda-test:
245246
name: "Python CUDA (test)"

‎scripts/post-ingest.py‎

Lines changed: 87 additions & 80 deletions
Original file line numberDiff line numberDiff line change
@@ -30,11 +30,9 @@
3030
import subprocess
3131
import sys
3232
import urllib.request
33-
from collections.abc import Callable
3433
from concurrent.futures import ThreadPoolExecutor
3534
from pathlib import Path
3635
from types import ModuleType
37-
from typing import TypeVar
3836

3937
# MUST equal `benchmarks-website/web/lib/schema-version.ts::SCHEMA_VERSION`.
4038
# Bumping this is a coordinated change across the website contract, v3.rs, and
@@ -217,8 +215,6 @@ def build_commit(sha: str, repo_url: str, git_dir: Path | None) -> dict:
217215
),
218216
}
219217

220-
_T = TypeVar("_T")
221-
222218
_MEASUREMENT_ID_MODULE: ModuleType | None = None
223219

224220

@@ -424,7 +420,7 @@ def _memory_quartet_consistent(r: dict) -> bool:
424420
def _validate_record_values(record: dict, kind: str, index: int) -> None:
425421
"""Validate every field's type/range before the Postgres write.
426422
427-
Runs in `ingest_postgres`'s loop, where the record index is known. It drives
423+
Runs before any transaction, where the record index is known. It drives
428424
type/range checks from `_FIELD_TYPES`, then applies semantic checks the type
429425
alone does not cover (the storage enum + memory quartet for query_measurements).
430426
"""
@@ -479,12 +475,9 @@ def _upsert_returning_was_update(conn, sql: str, params: tuple) -> bool:
479475
return not row[0]
480476

481477

482-
def _insert_query_measurement(conn, mid_mod, r: dict) -> bool:
483-
"""Upsert a `query_measurements` row.
484-
485-
Record values are validated by `_validate_record_values` before dispatch.
486-
"""
487-
mid = mid_mod.measurement_id_query(
478+
def _query_measurement_id(mid_mod, r: dict) -> int:
479+
"""Compute the `query_measurements` primary key."""
480+
return mid_mod.measurement_id_query(
488481
commit_sha=r["commit_sha"],
489482
dataset=r["dataset"],
490483
dataset_variant=r.get("dataset_variant"),
@@ -494,6 +487,13 @@ def _insert_query_measurement(conn, mid_mod, r: dict) -> bool:
494487
engine=r["engine"],
495488
format=r["format"],
496489
)
490+
491+
492+
def _insert_query_measurement(conn, mid: int, r: dict) -> bool:
493+
"""Upsert a `query_measurements` row.
494+
495+
Record values are validated by `_validate_record_values` before dispatch.
496+
"""
497497
return _upsert_returning_was_update(
498498
conn,
499499
"""
@@ -535,22 +535,26 @@ def _insert_query_measurement(conn, mid_mod, r: dict) -> bool:
535535
r.get("virtual_delta"),
536536
r.get("env_triple"),
537537
# The denormalized `commit_timestamp` (migration 006) is resolved from the
538-
# `commits` row this same transaction upserted first, so the read path's
539-
# latest-per-series summary never sees a NULL from this writer.
538+
# `commits` row that `ingest_postgres` commits before the fact transaction, so the
539+
# read path's latest-per-series summary never sees a NULL from this writer.
540540
r["commit_sha"],
541541
),
542542
)
543543

544544

545-
def _insert_compression_time(conn, mid_mod, r: dict) -> bool:
546-
"""Upsert a `compression_times` row."""
547-
mid = mid_mod.measurement_id_compression_time(
545+
def _compression_time_id(mid_mod, r: dict) -> int:
546+
"""Compute the `compression_times` primary key."""
547+
return mid_mod.measurement_id_compression_time(
548548
commit_sha=r["commit_sha"],
549549
dataset=r["dataset"],
550550
dataset_variant=r.get("dataset_variant"),
551551
format=r["format"],
552552
op=r["op"],
553553
)
554+
555+
556+
def _insert_compression_time(conn, mid: int, r: dict) -> bool:
557+
"""Upsert a `compression_times` row."""
554558
return _upsert_returning_was_update(
555559
conn,
556560
"""
@@ -579,14 +583,18 @@ def _insert_compression_time(conn, mid_mod, r: dict) -> bool:
579583
)
580584

581585

582-
def _insert_compression_size(conn, mid_mod, r: dict) -> bool:
583-
"""Upsert a `compression_sizes` row."""
584-
mid = mid_mod.measurement_id_compression_size(
586+
def _compression_size_id(mid_mod, r: dict) -> int:
587+
"""Compute the `compression_sizes` primary key."""
588+
return mid_mod.measurement_id_compression_size(
585589
commit_sha=r["commit_sha"],
586590
dataset=r["dataset"],
587591
dataset_variant=r.get("dataset_variant"),
588592
format=r["format"],
589593
)
594+
595+
596+
def _insert_compression_size(conn, mid: int, r: dict) -> bool:
597+
"""Upsert a `compression_sizes` row."""
590598
return _upsert_returning_was_update(
591599
conn,
592600
"""
@@ -612,14 +620,18 @@ def _insert_compression_size(conn, mid_mod, r: dict) -> bool:
612620
)
613621

614622

615-
def _insert_random_access(conn, mid_mod, r: dict) -> bool:
616-
"""Upsert a `random_access_times` row."""
617-
mid = mid_mod.measurement_id_random_access(
623+
def _random_access_time_id(mid_mod, r: dict) -> int:
624+
"""Compute the `random_access_times` primary key."""
625+
return mid_mod.measurement_id_random_access(
618626
commit_sha=r["commit_sha"],
619627
dataset=r["dataset"],
620628
format=r["format"],
621629
open_mode=r["open_mode"],
622630
)
631+
632+
633+
def _insert_random_access(conn, mid: int, r: dict) -> bool:
634+
"""Upsert a `random_access_times` row."""
623635
return _upsert_returning_was_update(
624636
conn,
625637
"""
@@ -648,19 +660,23 @@ def _insert_random_access(conn, mid_mod, r: dict) -> bool:
648660
)
649661

650662

651-
def _insert_vector_search(conn, mid_mod, r: dict) -> bool:
652-
"""Upsert a `vector_search_runs` row.
653-
654-
`threshold` is validated finite by `_validate_record_values` before dispatch.
655-
"""
656-
threshold = float(r["threshold"])
657-
mid = mid_mod.measurement_id_vector_search(
663+
def _vector_search_run_id(mid_mod, r: dict) -> int:
664+
"""Compute the `vector_search_runs` primary key."""
665+
return mid_mod.measurement_id_vector_search(
658666
commit_sha=r["commit_sha"],
659667
dataset=r["dataset"],
660668
layout=r["layout"],
661669
flavor=r["flavor"],
662-
threshold=threshold,
670+
threshold=float(r["threshold"]),
663671
)
672+
673+
674+
def _insert_vector_search(conn, mid: int, r: dict) -> bool:
675+
"""Upsert a `vector_search_runs` row.
676+
677+
`threshold` is validated finite by `_validate_record_values` before dispatch.
678+
"""
679+
threshold = float(r["threshold"])
664680
return _upsert_returning_was_update(
665681
conn,
666682
"""
@@ -698,8 +714,16 @@ def _insert_vector_search(conn, mid_mod, r: dict) -> bool:
698714
)
699715

700716

701-
# Dispatch from a record's `kind` to its per-table upsert. Keyed identically to
702-
# `_RECORD_FIELDS`; the two maps are wired together when adding a fact table.
717+
# Dispatch from a record's `kind` to its per-table primary key and upsert. Keyed identically to
718+
# `_RECORD_FIELDS`; the three maps are wired together when adding a fact table.
719+
_MEASUREMENT_ID = {
720+
"query_measurement": _query_measurement_id,
721+
"compression_time": _compression_time_id,
722+
"compression_size": _compression_size_id,
723+
"random_access_time": _random_access_time_id,
724+
"vector_search_run": _vector_search_run_id,
725+
}
726+
703727
_APPLY_RECORD = {
704728
"query_measurement": _insert_query_measurement,
705729
"compression_time": _insert_compression_time,
@@ -741,60 +765,42 @@ def _upsert_commit(conn, commit: dict) -> None:
741765
)
742766

743767

744-
# CI runs concurrent writers whose upserts can touch commits and dimensions in
745-
# conflicting orders. Retrying transaction-level deadlocks and serialization
746-
# failures keeps each JSONL file all-or-nothing.
747-
_WRITE_CONFLICT_ATTEMPTS = 128
768+
def ingest_postgres(conn, commit: dict, records: list[dict]) -> tuple[int, int]:
769+
"""Validate every record, then upsert the commit row and the file's fact rows.
748770
771+
Concurrent CI writers wait on each other only when their fact rows overlap, and never deadlock:
749772
750-
def _retry_write_conflicts(op: Callable[[], _T]) -> _T:
751-
"""Retry `op` on a Postgres write conflict.
773+
- The `commits` upsert commits in its own short transaction. Holding its row lock for the
774+
whole file would make every writer for the same commit queue behind the others. Commit
775+
metadata is a pure function of the SHA, and `commit-metadata.yml` already writes commit
776+
rows without fact rows, so this row may land even when the fact transaction fails.
777+
- The fact rows are upserted in one transaction, so a file lands all or nothing. They are
778+
applied in (table, `measurement_id`) order, so two writers whose rows overlap take their
779+
row locks in the same order and cannot deadlock.
752780
753-
Row-level `ON CONFLICT DO UPDATE` upserts touching the same commits or
754-
dimensions in conflicting orders can deadlock. The retryable Postgres errors are deadlock
755-
(`SQLSTATE 40P01`) and serialization failure (`40001`); both abort one transaction cleanly,
756-
so re-running the whole transaction is safe. A non-retryable error (e.g. a validation
757-
`SystemExit`) propagates immediately. Returns `op`'s value on the first success.
781+
Returns the number of fact rows inserted and updated.
758782
"""
759-
from psycopg import errors as pg_errors
760-
761-
for attempt in range(1, _WRITE_CONFLICT_ATTEMPTS + 1):
762-
try:
763-
return op()
764-
except (pg_errors.DeadlockDetected, pg_errors.SerializationFailure):
765-
# The failing `op`'s `with conn.transaction()` block already rolled back, so the
766-
# connection is idle and the whole transaction can be retried. Re-raise on the
767-
# final attempt.
768-
if attempt >= _WRITE_CONFLICT_ATTEMPTS:
769-
raise
770-
raise AssertionError("unreachable: _retry_write_conflicts exited without return or raise")
771-
783+
for idx, record in enumerate(records):
784+
kind = _validate_record_fields(record, idx)
785+
_validate_record_values(record, kind, idx)
786+
if record["commit_sha"] != commit["sha"]:
787+
raise SystemExit(
788+
f"record {idx} ({kind}): commit_sha {record['commit_sha']!r} does not "
789+
f"match the requested commit SHA {commit['sha']!r}"
790+
)
772791

773-
def ingest_postgres(conn, commit: dict, records: list[dict]) -> tuple[int, int]:
774-
"""Upsert a commit and its records into Postgres, retrying on write conflicts."""
775792
mid_mod = _measurement_id_module()
776-
return _retry_write_conflicts(lambda: _ingest_postgres_once(conn, commit, records, mid_mod))
777-
793+
rows = [(record["kind"], _MEASUREMENT_ID[record["kind"]](mid_mod, record), record) for record in records]
794+
rows.sort(key=lambda row: row[:2])
778795

779-
def _ingest_postgres_once(conn, commit: dict, records: list[dict], mid_mod) -> tuple[int, int]:
780-
"""Upsert a commit and its records in one transaction (a single attempt).
796+
with conn.transaction():
797+
_upsert_commit(conn, commit)
781798

782-
Upsert `commits` first, then each fact record while classifying it as inserted
783-
or updated. Any validation failure rolls the whole transaction back.
784-
"""
785799
inserted = 0
786800
updated = 0
787801
with conn.transaction():
788-
_upsert_commit(conn, commit)
789-
for idx, record in enumerate(records):
790-
kind = _validate_record_fields(record, idx)
791-
if record["commit_sha"] != commit["sha"]:
792-
raise SystemExit(
793-
f"record {idx} ({kind}): commit_sha {record['commit_sha']!r} does not "
794-
f"match the requested commit SHA {commit['sha']!r}"
795-
)
796-
_validate_record_values(record, kind, idx)
797-
if _APPLY_RECORD[kind](conn, mid_mod, record):
802+
for kind, mid, record in rows:
803+
if _APPLY_RECORD[kind](conn, mid, record):
798804
updated += 1
799805
else:
800806
inserted += 1
@@ -890,9 +896,12 @@ def connect_postgres(dsn: str, region: str | None) -> object:
890896
# migration-owned `public.*` tables, regardless of any `search_path` baked into the DSN's
891897
# `options=-c search_path=...` or the role's default. libpq applies repeated `-c` settings
892898
# left-to-right (last wins), so appending ours last makes it authoritative even if the DSN
893-
# already set one.
899+
# already set one. The connect and statement timeouts stop a network or server hang from
900+
# holding the job until its own timeout. `statement_timeout` also covers time spent waiting
901+
# for locks, so no separate `lock_timeout` is needed.
894902
existing_options = params.get("options") or ""
895-
params["options"] = f"{existing_options} -c search_path=public".strip()
903+
params["options"] = f"{existing_options} -c search_path=public -c statement_timeout=30000".strip()
904+
params["connect_timeout"] = 10
896905

897906
conn = psycopg.connect(**params)
898907
# Verify the RESOLVED transport actually used TLS, not merely that the DSN requested
@@ -970,9 +979,7 @@ def refresh_site_cache(base_url: str, token: str, timeout: float) -> None:
970979
def _main_postgres(args: argparse.Namespace) -> int:
971980
records = read_records(args.jsonl_path)
972981
# `build_commit` runs `git show <commit_sha>`, so the SHA must be in the runner's local git
973-
# history. The v4 ingest step inherits the v3 `--server` step's checkout assumption (the default
974-
# checkout provides the head SHA); a shallow checkout missing the SHA fails loud here, and the
975-
# v4 step is best-effort (continue-on-error), so it never fails the job.
982+
# history. A shallow checkout missing the SHA fails before opening a database connection.
976983
commit = build_commit(args.commit_sha, args.repo_url, args.git_dir)
977984
conn = connect_postgres(args.postgres, args.region)
978985
try:

0 commit comments

Comments
 (0)