spikerj/spikersoft-issues
1
0
Fork 0
Code Issues 196 Pull Requests Actions Packages Projects Releases Wiki Activity

[Bug][Infra][Data] Keycloak postgres pg_statistic toast corruption persists after #552 closed — ClearExpiredEvents/session purge failing every 15-min cycle since 02:29Z, repair never run #561

New Issue
Open
opened 2026-07-14 06:34:47 +00:00 by spikerj · 2 comments
spikerj commented 2026-07-14 06:34:47 +00:00
Owner
Copy Link
Copy Source

QA Team — 2026-07-14 ~06:40Z. #552 closed on the node-recovery story (fair — node and Keycloak tier are healthy), but the durable data damage from that incident was left unrepaired and is still failing live:

Current state (verified at filing time): missing chunk number 0 for toast value 131293 in pg_toast_2619 is still recurring in keycloak logs — multiple hits in the last 45 minutes, on the same ~15-minute scheduled-task cycle it has followed since 02:29:51Z. Every ClearExpiredEvents / session-purge run fails, so expired events and sessions have been accumulating for 4+ hours and will keep growing until repaired.

The repair (from #552's thread, unchanged): pg_toast_2619 is pg_statistic's toast table — derived planner statistics, safely rebuildable. As postgres superuser on the keycloak DB:

DELETE FROM pg_statistic;
ANALYZE;

(If the DELETE itself trips on the corrupt row: REINDEX TABLE pg_toast.pg_toast_2619; first.)
Afterwards, a one-time pg_dump > /dev/null sweep is cheap insurance that pg_statistic was the only casualty — it was merely the visible one (the table the scheduled task happens to read).

Verification once run: the error stops appearing on the next scheduled cycle (watch ~15 min), and the accumulated EVENT_ENTITY backlog drains.

Refs: #552 (closed — incident that wrote through the dead CIFS mount and caused this), #555 (the alerting gap that let it happen silently).

**QA Team** — 2026-07-14 ~06:40Z. #552 closed on the node-recovery story (fair — node and Keycloak tier are healthy), but the **durable data damage from that incident was left unrepaired** and is still failing live: **Current state (verified at filing time):** `missing chunk number 0 for toast value 131293 in pg_toast_2619` is still recurring in keycloak logs — multiple hits in the last 45 minutes, on the same ~15-minute scheduled-task cycle it has followed since 02:29:51Z. Every `ClearExpiredEvents` / session-purge run fails, so **expired events and sessions have been accumulating for 4+ hours** and will keep growing until repaired. **The repair (from #552's thread, unchanged):** `pg_toast_2619` is `pg_statistic`'s toast table — derived planner statistics, safely rebuildable. As postgres superuser on the keycloak DB: ```sql DELETE FROM pg_statistic; ANALYZE; ``` (If the DELETE itself trips on the corrupt row: `REINDEX TABLE pg_toast.pg_toast_2619;` first.) Afterwards, a one-time `pg_dump > /dev/null` sweep is cheap insurance that pg_statistic was the only casualty — it was merely the *visible* one (the table the scheduled task happens to read). **Verification once run:** the error stops appearing on the next scheduled cycle (watch ~15 min), and the accumulated EVENT_ENTITY backlog drains. **Refs:** #552 (closed — incident that wrote through the dead CIFS mount and caused this), #555 (the alerting gap that let it happen silently).
spikerj commented 2026-07-14 17:42:49 +00:00
Author
Owner
Copy Link
Copy Source

QA Team — sweep 2026-07-14 ~17:45Z. Still firing, ~15h after this ticket was opened, and the error has moved.

Latest occurrence, straight off keycloak_keycloak on dreamstream5 at 17:29:50Z today:

ERROR [org.keycloak.services.scheduled.ScheduledTaskRunner] (Timer-0)
  Failed to run scheduled task ClearExpiredEvents:
  org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL
  [ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619]
  [select ee1_0.ID from public.EVENT_ENTITY ee1_0
   where ee1_0.REALM_ID=? and ee1_0.EVENT_TIME<?
   order by ee1_0.EVENT_TIME fetch first ? rows only for no key update of ee1_0 skip locked]
Caused by: org.postgresql.util.PSQLException:
  ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619

Worth flagging precisely, because it sharpens the diagnosis in this ticket:

  • pg_toast_2619 is the TOAST table for pg_statistic — so this is the same corruption this ticket already names, now confirmed still present and still unrepaired.
  • The failing statement is ClearExpiredEvents, Keycloak's scheduled purge of EVENT_ENTITY. It is failing on every cycle, not intermittently.

The consequence nobody has written down yet: while this is broken, expired Keycloak events are never purged, so EVENT_ENTITY grows without bound. We measured keycloak-events ingest at ~113 docs/day; the postgres-side EVENT_ENTITY table has the same problem with no cleanup running at all. This is a slow-motion disk issue on top of the correctness issue — and SERVER's root filesystem hitting 94% (#558, closed today) is a reminder of what that ends in.

The repair is still the same and still has not been run: the planner touching a corrupt pg_statistic toast entry needs ANALYZE on the affected relation to rewrite the stats (or VACUUM FULL pg_statistic / in the worst case REINDEX). Until someone runs it, this fires every 15 minutes indefinitely.

Note this predates and is independent of the dreamstream4 failover in #552 (closed) — that closed on the node recovering, but the database corruption it left behind is what this ticket is about, and it is untouched.

**QA Team** — sweep 2026-07-14 ~17:45Z. **Still firing, ~15h after this ticket was opened, and the error has moved.** Latest occurrence, straight off `keycloak_keycloak` on dreamstream5 at **17:29:50Z today**: ``` ERROR [org.keycloak.services.scheduled.ScheduledTaskRunner] (Timer-0) Failed to run scheduled task ClearExpiredEvents: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619] [select ee1_0.ID from public.EVENT_ENTITY ee1_0 where ee1_0.REALM_ID=? and ee1_0.EVENT_TIME<? order by ee1_0.EVENT_TIME fetch first ? rows only for no key update of ee1_0 skip locked] Caused by: org.postgresql.util.PSQLException: ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619 ``` Worth flagging precisely, because it sharpens the diagnosis in this ticket: - `pg_toast_2619` is the TOAST table for **`pg_statistic`** — so this is the same corruption this ticket already names, now confirmed still present and still unrepaired. - The failing statement is **`ClearExpiredEvents`**, Keycloak's scheduled purge of `EVENT_ENTITY`. It is failing on *every* cycle, not intermittently. **The consequence nobody has written down yet:** while this is broken, expired Keycloak events are never purged, so `EVENT_ENTITY` grows without bound. We measured keycloak-events ingest at ~113 docs/day; the postgres-side `EVENT_ENTITY` table has the same problem with no cleanup running at all. This is a slow-motion disk issue on top of the correctness issue — and SERVER's root filesystem hitting 94% (#558, closed today) is a reminder of what that ends in. The repair is still the same and still has not been run: the planner touching a corrupt `pg_statistic` toast entry needs `ANALYZE` on the affected relation to rewrite the stats (or `VACUUM FULL pg_statistic` / in the worst case `REINDEX`). Until someone runs it, this fires every 15 minutes indefinitely. Note this predates and is independent of the dreamstream4 failover in #552 (closed) — that closed on the *node* recovering, but the database corruption it left behind is what this ticket is about, and it is untouched.
spikerj commented 2026-07-29 15:56:17 +00:00
Author
Owner
Copy Link
Copy Source

Audited against origin/master — NOT DONE, and this is the same live defect as #482, not a distinct one. Recommending a merge.

Same corruption, same object, same failing job. Both tickets name the identical error — missing chunk number 0 for toast value 131293 in pg_toast_2619 — including the same toast value 131293, and both fail on ClearExpiredEvents. That's one corruption event observed twice, not a recurring class.

The only artifact in git is the runbook, and it has never been touched since it landed: spikersoft-infrastructure/docs/keycloak-postgres-toast-recovery.md, single commit 8ee2a8d (2026-07-12, PR #49). git log origin/master -- docs/keycloak-postgres-toast-recovery.md returns that one commit and nothing after. git log origin/master --grep='#561' across all four repos returns zero commits.

What genuinely distinguishes the two: #482 (filed 07-12) predates the runbook and carries the SHOW data_checksums; root-cause tail plus the link to #503's confirmed bad RAM on the same host. #561 (filed 07-14) is the post-#552 re-observation and adds the consequence that matters operationally — unbounded EVENT_ENTITY growth, because the purge cycle never completes.

Suggested disposition: keep #482 as the survivor (it holds the runbook link and the bad-RAM root-cause thread), fold this ticket's EVENT_ENTITY-growth note into it, and close this one as a duplicate. I've left both open rather than deciding that unilaterally.

Remaining either way: run the runbook (DELETE FROM pg_statistic; VACUUM pg_statistic; ANALYZE;), confirm the ~60s error stream stops, confirm ClearExpiredEvents completes and EVENT_ENTITY drains, then run SHOW data_checksums;. Git cannot prove any of that — it's a one-time superuser action against a live database. What would settle it: Keycloak service logs showing a clean ClearExpiredEvents cycle, or simply an execution note on the ticket.

That last point is the actual lesson here. This has now been re-verified from scratch at least three times because there is no place where "ran the runbook" gets written down.

Audited against `origin/master` — **NOT DONE, and this is the same live defect as #482, not a distinct one.** Recommending a merge. **Same corruption, same object, same failing job.** Both tickets name the identical error — `missing chunk number 0 for toast value 131293 in pg_toast_2619` — including the same toast value `131293`, and both fail on `ClearExpiredEvents`. That's one corruption event observed twice, not a recurring class. **The only artifact in git is the runbook**, and it has never been touched since it landed: `spikersoft-infrastructure/docs/keycloak-postgres-toast-recovery.md`, single commit `8ee2a8d` (2026-07-12, PR #49). `git log origin/master -- docs/keycloak-postgres-toast-recovery.md` returns that one commit and nothing after. `git log origin/master --grep='#561'` across all four repos returns **zero commits**. **What genuinely distinguishes the two:** #482 (filed 07-12) predates the runbook and carries the `SHOW data_checksums;` root-cause tail plus the link to #503's confirmed bad RAM on the same host. #561 (filed 07-14) is the post-#552 re-observation and adds the consequence that matters operationally — unbounded `EVENT_ENTITY` growth, because the purge cycle never completes. **Suggested disposition:** keep **#482** as the survivor (it holds the runbook link and the bad-RAM root-cause thread), fold this ticket's `EVENT_ENTITY`-growth note into it, and close this one as a duplicate. I've left both open rather than deciding that unilaterally. **Remaining either way:** run the runbook (`DELETE FROM pg_statistic; VACUUM pg_statistic; ANALYZE;`), confirm the ~60s error stream stops, confirm `ClearExpiredEvents` completes and `EVENT_ENTITY` drains, then run `SHOW data_checksums;`. Git cannot prove any of that — it's a one-time superuser action against a live database. What would settle it: Keycloak service logs showing a clean `ClearExpiredEvents` cycle, or simply an execution note on the ticket. That last point is the actual lesson here. This has now been re-verified from scratch at least three times because there is no place where "ran the runbook" gets written down.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
agentic
Actionable surfaced by the agentic architecture & security review
 bug
Something is not working
 duplicate
This issue or pull request already exists
 enhancement
New feature
 help wanted
Need some help
 invalid
Something is wrong
 question
More information is needed
 sonarqube
Code quality issue surfaced by SonarQube (bug / code smell)
 wontfix
This won't be fixed
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
spikerj (Joseph Spiker)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: spikerj/spikersoft-issues#561