Deleting a playlist that devices use fails with an internal error #223

Closed
opened 2026-11-15 16:52:59 +00:00 by vivy · 7 comments
Owner

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.

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.
Member

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.

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.
Author
Owner

In dev via PR #246 (verified with fj landed). Closing for the Monday demo.

In dev via PR #246 (verified with fj landed). Closing for the Monday demo.
vivy closed this issue 2026-11-15 20:55:36 +00:00
Author
Owner

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:

auth: 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" on table "device_playlist_assignments"

Cause: the server runs PostgreSQL 18.6 (the Ubuntu 26.04 package). device_playlist_assignments.playlist_id is ON DELETE RESTRICT (0001_initial_schema.sql:131). PostgreSQL 18 reports a RESTRICT violation as SQLSTATE 23001 restrict_violation. PostgreSQL 16 reported it as 23503 foreign_key_violation. is_foreign_key_violation (playlists/handlers.rs:1496) matches only 23503, so the 409 path in delete_playlist is never taken, the panel never gets device_assigned, and it never offers "delete and unassign".

Checked: on this build host (PostgreSQL 16.15) the same RESTRICT delete gives 23503. CI runs postgres:16, which is why the #223 tests are green.

Fix:

  1. One shared helper that matches both 23503 and 23001; use it in playlists/handlers.rs, media/handlers.rs and campus.rs (three copies today).
  2. A test that runs on PostgreSQL 18 — the version our target platform ships. Either run the Postgres suite on postgres:18 in 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 the gs AppArmor 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.

**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: ``` auth: 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" on table "device_playlist_assignments" ``` **Cause:** the server runs PostgreSQL **18.6** (the Ubuntu 26.04 package). `device_playlist_assignments.playlist_id` is `ON DELETE RESTRICT` (`0001_initial_schema.sql:131`). PostgreSQL 18 reports a RESTRICT violation as SQLSTATE **23001** `restrict_violation`. PostgreSQL 16 reported it as **23503** `foreign_key_violation`. `is_foreign_key_violation` (`playlists/handlers.rs:1496`) matches only `23503`, so the 409 path in `delete_playlist` is never taken, the panel never gets `device_assigned`, and it never offers "delete and unassign". Checked: on this build host (PostgreSQL 16.15) the same RESTRICT delete gives `23503`. CI runs `postgres:16`, which is why the #223 tests are green. **Fix:** 1. One shared helper that matches both `23503` and `23001`; use it in `playlists/handlers.rs`, `media/handlers.rs` and `campus.rs` (three copies today). 2. A test that runs on PostgreSQL 18 — the version our target platform ships. Either run the Postgres suite on `postgres:18` in 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 the `gs` AppArmor 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.
vivy reopened this issue 2026-11-17 12:39:28 +00:00
Member

Fix in PR #309: one shared is_foreign_key_violation (now in db.rs, used by campus.rs, media/handlers.rs and playlists/handlers.rs) matching both 23503 and 23001.

Worth flagging before this is taken as closing the loop: I could not reproduce SQLSTATE 23001 here. This build host's own PostgreSQL is 18.6, and a direct ON DELETE RESTRICT violation against it — both a minimal repro and the full playlist_integration/campus_integration/media_integration suites run live against it — consistently reports 23503, 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 \errverbose capture 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, and cargo test --workspace --locked against the live local PostgreSQL 18.6 — all green.

CI on #309 is queued (eta ~45m per fj ci).

Fix in PR #309: one shared `is_foreign_key_violation` (now in `db.rs`, used by `campus.rs`, `media/handlers.rs` and `playlists/handlers.rs`) matching both `23503` and `23001`. Worth flagging before this is taken as closing the loop: I could not reproduce SQLSTATE `23001` here. This build host's own PostgreSQL is 18.6, and a direct `ON DELETE RESTRICT` violation against it — both a minimal repro and the full `playlist_integration`/`campus_integration`/`media_integration` suites run live against it — consistently reports `23503`, 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 `\errverbose` capture 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`, and `cargo test --workspace --locked` against the live local PostgreSQL 18.6 — all green. CI on #309 is queued (eta ~45m per `fj ci`).
Author
Owner

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.

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.
Author
Owner

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 (dev aeab4f8), and aeab4f8 is now deployed on that server. Open until the delete is confirmed in the panel.

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 (dev aeab4f8), and aeab4f8 is now deployed on that server. Open until the delete is confirmed in the panel.
vivy closed this issue 2026-11-17 17:36:57 +00:00
Author
Owner

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).

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).
Sign in to join this conversation.
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#223
No description provided.