GRI-2180: match restrict_violation (23001) alongside foreign_key_violation (23503) #309

Merged
vivy merged 3 commits from fix/GRI-2180-pg18-restrict-violation-code into dev 2026-11-17 14:12:55 +00:00
Member

Summary

Forge issue #223 was reopened: delete_playlist (and delete_media,
delete_campus) each kept their own copy of is_foreign_key_violation,
matching only SQLSTATE 23503. On the campus test server, which runs
PostgreSQL 18 from the Ubuntu 26.04 package, the reopening comment
suspected the RESTRICT violation surfaces as 23001 instead, so the 409
path was never taken and the raw database error passed through delete_playlist's handler as a 500.

  • Added one shared is_foreign_key_violation in db.rs, matching both
    23503 and 23001, and removed the three private copies in
    campus.rs, media/handlers.rs and playlists/handlers.rs in favour
    of it.
  • Added unit tests in db.rs pinning the predicate against both codes
    (and against an unrelated code and a non-Database error), using a
    hand-rolled DatabaseError impl since sqlx's real one is not
    publicly constructible outside the driver.

Caveat worth flagging to whoever verifies this on the campus server:
I could not reproduce SQLSTATE 23001 locally. This build host's own
PostgreSQL is in fact 18.6, and a direct ON DELETE RESTRICT violation
against it — both through a minimal reproduction and through the full
playlist_integration/campus_integration/media_integration suites
run against it live — consistently reports 23503, the same code and
the same message shape as PostgreSQL 16. So the real SQLSTATE the
campus server returns is still unconfirmed; this change is a safe,
zero-cost broadening that covers the suspected code either way, but if
the campus server is still returning 500 after this lands, the next
step is a direct \errverbose capture there rather than another guess
at the code.

I did not touch .github/workflows/ci.yml (the issue's "run the suite
against postgres:18 too" suggestion) — CI configuration is outside what
I'm permitted to change; flagged to the chief of staff separately.

Test plan

  • cargo test -p server --lib db:: — new unit tests, both SQLSTATEs
  • cargo clippy --workspace --all-targets --locked -- -D warnings
  • cargo fmt --all --check
  • cargo test --workspace --locked against a live PostgreSQL 18.6
    (SIGNAGE_TEST_DATABASE_URL=postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable) — all green, including
    deleting_a_playlist_assigned_to_a_device_is_refused,
    deleting_a_campus_still_referenced_by_media_is_refused, and the
    media-in-use tests
  • Verify on the actual campus test server (10.4.80.205) — I have no
    access to it
## Summary Forge issue #223 was reopened: `delete_playlist` (and `delete_media`, `delete_campus`) each kept their own copy of `is_foreign_key_violation`, matching only SQLSTATE `23503`. On the campus test server, which runs PostgreSQL 18 from the Ubuntu 26.04 package, the reopening comment suspected the RESTRICT violation surfaces as `23001` instead, so the 409 path was never taken and the raw database error passed through `delete_playlist`'s handler as a 500. - Added one shared `is_foreign_key_violation` in `db.rs`, matching both `23503` and `23001`, and removed the three private copies in `campus.rs`, `media/handlers.rs` and `playlists/handlers.rs` in favour of it. - Added unit tests in `db.rs` pinning the predicate against both codes (and against an unrelated code and a non-`Database` error), using a hand-rolled `DatabaseError` impl since `sqlx`'s real one is not publicly constructible outside the driver. **Caveat worth flagging to whoever verifies this on the campus server:** I could not reproduce SQLSTATE `23001` locally. This build host's own PostgreSQL is in fact 18.6, and a direct `ON DELETE RESTRICT` violation against it — both through a minimal reproduction and through the full `playlist_integration`/`campus_integration`/`media_integration` suites run against it live — consistently reports `23503`, the same code and the same message shape as PostgreSQL 16. So the real SQLSTATE the campus server returns is still unconfirmed; this change is a safe, zero-cost broadening that covers the suspected code either way, but if the campus server is still returning 500 after this lands, the next step is a direct `\errverbose` capture there rather than another guess at the code. I did not touch `.github/workflows/ci.yml` (the issue's "run the suite against postgres:18 too" suggestion) — CI configuration is outside what I'm permitted to change; flagged to the chief of staff separately. ## Test plan - [x] `cargo test -p server --lib db::` — new unit tests, both SQLSTATEs - [x] `cargo clippy --workspace --all-targets --locked -- -D warnings` - [x] `cargo fmt --all --check` - [x] `cargo test --workspace --locked` against a live PostgreSQL 18.6 (`SIGNAGE_TEST_DATABASE_URL=postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable`) — all green, including `deleting_a_playlist_assigned_to_a_device_is_refused`, `deleting_a_campus_still_referenced_by_media_is_refused`, and the media-in-use tests - [ ] Verify on the actual campus test server (10.4.80.205) — I have no access to it
GRI-2180: match restrict_violation (23001) alongside foreign_key_violation (23503)
Some checks failed
CI / the pre-push hook (pull_request) Successful in 4s
CI / cargo fmt (pull_request) Successful in 4s
CI / install.sh client upgrade (pull_request) Successful in 14s
CI / cargo deny (pull_request) Successful in 1m4s
CI / cargo deny advisories (pull_request) Failing after 58s
CI / repository boundary (pull_request) Successful in 4s
CI / cargo clippy (pull_request) Successful in 2m18s
CI / frontend-web (pull_request) Successful in 1m21s
CI / cargo test (pull_request) Successful in 6m14s
CI / CI (pull_request) Failing after 1s
CI / Notify gate failed (pull_request) Successful in 2s
28163dff2d
delete_playlist, delete_media and delete_campus each kept their own copy
of is_foreign_key_violation, matching only SQLSTATE 23503. Forge issue
#223 was reopened: on the campus test server (PostgreSQL 18 via the
Ubuntu 26.04 package) that delete's RESTRICT violation was suspected to
surface under 23001 instead, so the 409 path was never taken and the
raw database error passed through as a 500.

Shares one is_foreign_key_violation in db.rs, matching both codes, used
by all three handlers.
Merge branch 'dev' into fix/GRI-2180-pg18-restrict-violation-code
All checks were successful
CI / the pre-push hook (pull_request) Successful in 4s
CI / install.sh client upgrade (pull_request) Successful in 16s
CI / cargo fmt (pull_request) Successful in 5s
CI / cargo deny (pull_request) Successful in 35s
CI / cargo clippy (pull_request) Successful in 1m57s
CI / cargo deny advisories (pull_request) Successful in 31s
CI / repository boundary (pull_request) Successful in 5s
CI / frontend-web (pull_request) Successful in 1m26s
CI / cargo test (pull_request) Successful in 5m38s
CI / Notify gate failed (pull_request) Has been skipped
CI / CI (pull_request) Successful in 1s
e1ec962cf2
Merge branch 'dev' into fix/GRI-2180-pg18-restrict-violation-code
All checks were successful
CI / the pre-push hook (pull_request) Successful in 3s
CI / install.sh client upgrade (pull_request) Successful in 15s
CI / cargo fmt (pull_request) Successful in 3s
CI / cargo deny (pull_request) Successful in 33s
CI / cargo clippy (pull_request) Successful in 1m54s
CI / cargo deny advisories (pull_request) Successful in 44s
CI / repository boundary (pull_request) Successful in 5s
CI / frontend-web (pull_request) Successful in 1m26s
CI / cargo test (pull_request) Successful in 5m43s
CI / CI (pull_request) Successful in 1s
CI / Notify gate failed (pull_request) Has been skipped
c9fda46de6
vivy merged commit aeab4f831b into dev 2026-11-17 14:12:55 +00:00
vivy deleted branch fix/GRI-2180-pg18-restrict-violation-code 2026-11-17 14:12:56 +00:00
Sign in to join this conversation.
No reviewers
No milestone
No project
No assignees
2 participants
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
Grimoirix/grimoire-of-digital-signage!309
No description provided.