Repository navigation
postgres_integer_overflow_primary_key problem (astronomy-shop) - #1005
Saadmrp1038 merged 25 commits into
Conversation
|
/evaluate-problem integer_overflow_primary_key_astronomy_shop |
Z.ai Problem Difficulty EvaluationNo result CSV was produced. Inspect the benchmark and cluster logs. |
|
/evaluate-problem integer_overflow_primary_key_astronomy_shop |
Problem Difficulty Evaluation❌ Failed before a pass-rate report was produced. Inspect the workflow run for the preflight or infrastructure error. |
Z.ai Problem Difficulty Evaluation
Overall requires both diagnosis and mitigation to pass. Infrastructure-incomplete attempts are not counted as model failures.
Screening decision |
|
/evaluate-problem integer_overflow_primary_key_astronomy_shop |
Problem Difficulty EvaluationNo result CSV was produced. Inspect the benchmark and cluster logs. |
|
/evaluate-problem integer_overflow_primary_key_astronomy_shop |
Z.ai Problem Difficulty Evaluation
Overall requires both diagnosis and mitigation to pass. Infrastructure-incomplete attempts are not counted as model failures.
Screening decision |
|
@saihariG please resolve @Saadmrp1038's comments and request another review. |
|
/evaluate-problem integer_overflow_primary_key_astronomy_shop |
Z.ai Problem Difficulty Evaluation
Overall requires both diagnosis and mitigation to pass. Infrastructure-incomplete attempts are not counted as model failures.
Screening decision |
…ttps://github.com/saihariG/SREGym into problem/integer-overflow-primary-key-astronomy-shop
There was a problem hiding this comment.
@saihariG Thanks for the updates! After some more testing I found some other issues.
| Scenario | Expected | Actual |
|---|---|---|
| Add new reviews, then delete all 50 original reviews | Fail: original data must survive | Pass: the row count and sentinel checks do not detect the lost reviews |
| Repair the sequence but remove the application user’s INSERT permission | Fail: application writes are still broken | Pass: the root probe succeeds while the writer repeatedly reports permission errors |
| Remove the primary key without equivalent uniqueness protection | Fail: review IDs must remain unique | Pass: the database also accepts an INSERT with an existing ID |
| Reset the sequence toward occupied IDs | Fail: the available range is not collision-free | Pass: the active writer starts reporting duplicate-ID errors 29 seconds later |
| Use a descending sequence through unused negative IDs | Pass: writes work and substantial unused capacity remains | Fail: the headroom calculation assumes an ascending sequence |
| Rename the identity sequence after a correct BIGINT repair | Pass: the identity column and writes still work | Fail: the oracle looks for the old sequence name |
| Read the injected sentinel row | No explicit fault or oracle labels | The agent can read seq_overflow_sentinel and fault marker row; do not delete (just make it look like a normal review) |
I think creating a foolproof mitigation oracle for this fault that guarantees all future writes will succeed indefinitely is not realistic. Fixing possible edge cases should be sufficient.
Thanks for the thorough testing @Saadmrp1038 ! I reworked the oracle and stress-tested covering all the scenarios.
Also I verified locally that all valid fixes still pass, and every cheat is rejected. |
|
/evaluate-problem integer_overflow_primary_key_astronomy_shop |
Z.ai Problem Difficulty Evaluation
Overall requires both diagnosis and mitigation to pass. Infrastructure-incomplete attempts are not counted as model failures.
Screening decision |
|
LGTM. |
Implements #971
1. Real-world Failure Story
An
INTEGER(int4) primary key or identity sequence has a hard ceiling of 2,147,483,647. On a high-write table the sequence eventually exhausts, and every newINSERTthat allocates an id vianextval()begins failing withreached maximum value of sequence, while existing reads keep working, the classic "database is up but half-broken" outage that fools responders looking at pod/CPU health.Relevant PostgreSQL behavior is documented under numeric types and sequence functions.
2. Simulation on SREGym
App: astronomy-shop
The
product-reviewsservice writes toreviews.productreviews, whoseidis an INTEGER GENERATED BY DEFAULT AS IDENTITY column backed byreviews.productreviews_id_seq.inject_fault(): pins the sequence at int4 max (setval(seq, 2147483647, true)) so the next nextval() overflows, then confirms that a write actually fails and raises if the fault did not take.recover_fault(): applies the durable real-world fix, a schema migration widening the column and sequence toBIGINT (ALTER COLUMN id TYPE bigint + ALTER SEQUENCE ... AS bigint), then verifies writes succeed again.Mitigation oracle: a rollback-wrappedINSERTprobe (leaves no data) that accepts the fix only when a write succeeds. The default pod-health oracle cannot detect this since every pod stays Running.3. Problem run-time behavior
ERROR: nextval: reached maximum value of sequence "productreviews_id_seq" (2147483647).product-reviewsandpostgresqlpods stay Running with normal CPU and memory.idcolumn's type and the sequence'slast_value), not from pod health.Notes
BIGINTmigration (the real SRE fix). A sequence reset is only a safe band-aid when the id space is collision-free.astronomy-shopis redeployed fresh each run, so the exhausted sequence never leaks across runs.INSERTto detect exhaustion, so an expectedERRORline appears in the validation log on the exhausted path./validate-problem integer_overflow_primary_key_astronomy_shop