Deleting a playlist that devices use fails with an internal error #223
Labels
No labels
Area/Backend
Area/Database
Area/Docs
Area/Frontend
Area/Infra
Area/Security
Area/Tests
Area/Tooling
Compat/Breaking
Kind/Bug
Kind/Documentation
Kind/Enhancement
Kind/Feature
Kind/Security
Kind/Testing
Lang/Python
Lang/Rust
Lang/Shell
Lang/SQL
Lang/TypeScript
Priority
Critical
Priority
High
Priority
Low
Priority
Medium
Reviewed
Confirmed
Reviewed
Duplicate
Reviewed
Invalid
Reviewed
Won't Fix
Routing
Auto
Routing
Hold
Routing
Human
Status
Abandoned
Status
Blocked
Status
Need More Info
No milestone
No project
No assignees
2 participants
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
Grimoirix/grimoire-of-digital-signage#223
Loading…
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Found by the product owner on the test server (dev
8ed0ea1), GRI-2051.Server log 16:38:41 UTC:
deleting the playlist: error returned from database: update or delete on table "playlists" violates RESTRICT setting of foreign key constraint "device_playlist_assignments_playlist_id_fkey". The handler passes the DB error through as 500.Expected: no 500. Proposal, same shape as campus delete (#207) and media-in-use: the panel asks "N devices show this playlist. Delete it and set them to no playlist?"; on confirm the server unassigns those devices (push PlaylistCleared, as #209 does) and deletes, in one transaction. A test for a playlist with assigned devices, online and offline.
Follow-up from vivy's review of #246, not blocking: in delete_unassigning_devices, the SELECT of assigned devices and the DELETE of device_playlist_assignments are two statements under READ COMMITTED. A device assigned to the playlist between those two statements is unassigned (its row is deleted by the second statement) but never gets a PlaylistCleared push, since it wasn't in the SELECT's result set. A DELETE FROM device_playlist_assignments ... RETURNING device_id joined to devices would close that race by doing the removal and the enumeration in one statement. Worth a small follow-up issue if we want to close it.
In dev via PR #246 (verified with fj landed). Closing for the Monday demo.
Reopened: the #223 fix does not work on Ubuntu 26.04. Reproduced on the campus test server (10.4.80.205) on 2026-11-17, playlist "test" with one assigned device ("scryglass Test Client"). Four tries between 12:14 and 12:35 UTC; every one is a 500 "an internal error occurred", never the 409 confirm.
Server log:
Cause: the server runs PostgreSQL 18.6 (the Ubuntu 26.04 package).
device_playlist_assignments.playlist_idisON DELETE RESTRICT(0001_initial_schema.sql:131). PostgreSQL 18 reports a RESTRICT violation as SQLSTATE 23001restrict_violation. PostgreSQL 16 reported it as 23503foreign_key_violation.is_foreign_key_violation(playlists/handlers.rs:1496) matches only23503, so the 409 path indelete_playlistis never taken, the panel never getsdevice_assigned, and it never offers "delete and unassign".Checked: on this build host (PostgreSQL 16.15) the same RESTRICT delete gives
23503. CI runspostgres:16, which is why the #223 tests are green.Fix:
23503and23001; use it inplaylists/handlers.rs,media/handlers.rsandcampus.rs(three copies today).postgres:18in CI, or on both 16 and 18. This is the second time a difference between the build host and Ubuntu 26.04 hid a bug (the first was thegsAppArmor profile in #229).Done when: deleting a playlist with an assigned device on the campus test server shows the confirm, and "delete anyway" deletes it and unassigns the device.
Workaround until then: unassign the device from the playlist first, then delete.
Fix in PR #309: one shared
is_foreign_key_violation(now indb.rs, used bycampus.rs,media/handlers.rsandplaylists/handlers.rs) matching both23503and23001.Worth flagging before this is taken as closing the loop: I could not reproduce SQLSTATE
23001here. This build host's own PostgreSQL is 18.6, and a directON DELETE RESTRICTviolation against it — both a minimal repro and the fullplaylist_integration/campus_integration/media_integrationsuites run live against it — consistently reports23503, same code and same message shape as 16 ("violates foreign key constraint ...", not "violates RESTRICT setting of foreign key constraint ..."). So the campus server's actual SQLSTATE is still unconfirmed on my end; this change is a safe, zero-cost broadening that covers the suspected code either way, but if it's still 500 after this lands, the next step there is a direct\errverbosecapture on 10.4.80.205 rather than another guess.Did not touch
.github/workflows/ci.yml(the "run the suite on postgres:18 too" part) — CI configuration is outside what I'm permitted to change; flagging that to the chief of staff separately.Verified:
cargo test -p server --lib db::(new unit tests pinning both codes),cargo clippy --workspace --all-targets --locked -- -D warnings,cargo fmt --all --check, andcargo test --workspace --lockedagainst the live local PostgreSQL 18.6 — all green.CI on #309 is queued (eta ~45m per
fj ci).PR #309 (match 23001 as well as 23503) is updated with dev at
c9fda46, CI re-running; I merge it on green. It does not close #223: the PR itself reports that a direct ON DELETE RESTRICT test on PostgreSQL 18.6 still gives 23503, so 23001 may not be the cause. #223 stays open until deleting an assigned playlist is verified on the campus test server (10.4.80.205), and if it still fails there, the real SQLSTATE comes from that server log.Measured on the campus test server 10.4.80.205 (PostgreSQL 18.6), 2026-11-17 15:16 UTC, in a transaction that was rolled back:
DELETE FROM playlists WHERE name='test'fails with SQLSTATE 23001 (violates RESTRICT setting of foreign key constraint device_playlist_assignments_playlist_id_fkey). That constraint has confdeltype 'r' (RESTRICT) on this server. So the 23001 cause holds here; the 23503 seen in the #309 test likely comes from a NO ACTION constraint in that test schema. #309 matches both codes and is merged (devaeab4f8), andaeab4f8is now deployed on that server. Open until the delete is confirmed in the panel.Verified by the product owner on the campus test server (2026-11-17 17:35 UTC): the assigned playlist "test" was deleted through the panel. Fix: #309 (accepts SQLSTATE 23001 from PostgreSQL 18 RESTRICT as well as 23503).