[Bug][Infra] Keycloak Postgres TOAST corruption (pg_toast_2619 / pg_statistic) — ClearExpiredEvents failing, events not purged #482

New Issue

2026-07-12T00:04:27Z

spikerj commented

2026-07-12 00:04:27 +00:00

Summary

The Keycloak PostgreSQL database has a corrupted TOAST value in pg_toast_2619 (the TOAST table backing the pg_statistic planner-statistics catalog). Every query whose plan needs the affected column statistics fails, and Keycloak's ClearExpiredEvents scheduled job fails on every run — so expired Keycloak events are no longer being purged (unbounded EVENT_ENTITY growth).

Symptoms

Postgres side (keycloak_postgres-keycloak, currently on dreamstream4), recurring ~every 60s:

ERROR:  missing chunk number 0 for toast value 131293 in pg_toast_2619

Keycloak side (keycloak_keycloak), ~every 7.5 min:

ERROR [org.keycloak.services.scheduled.ScheduledTaskRunner] Failed to run scheduled task ClearExpiredEvents:
org.hibernate.exception.GenericJDBCException: ... [ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619]

Scope observed over 60 min: a single toast value (131293), single toast table (pg_toast_2619 = pg_statistic), single failing task (ClearExpiredEvents).

What this means

pg_toast_2619 is the TOAST relation for the system catalog pg_statistic. The corruption is in cached planner statistics, not in real Keycloak user/realm data — which makes it safe to remediate by regenerating statistics. But the planner keeps reading the dead toast pointer, so any statement that touches those stats errors out until the stale row is cleared.

Impact

ClearExpiredEvents never completes -> EVENT_ENTITY grows without bound (DB bloat over time).
Any planner path needing the corrupt column stats can fail intermittently.

Proposed remediation (take a backup first)

Because the corruption is confined to pg_statistic, regenerating statistics clears it:

-- as a superuser, against the keycloak DB
-- (optional targeted: ANALYZE each table until the erroring one is found, which overwrites its stat rows)
-- reliable blanket fix:
DELETE FROM pg_statistic;   -- may require: SET allow_system_table_mods = on;
ANALYZE;                    -- regenerates all statistics from scratch

Then confirm the missing chunk errors stop and ClearExpiredEvents succeeds.

Root-cause follow-up

TOAST corruption usually traces to an unclean stop or a storage-layer problem:

Check dmesg / SMART on dreamstream4 (and wherever this PG volume has run) for disk errors.
Verify whether data_checksums is enabled (SHOW data_checksums;) — if off, consider enabling on the next rebuild so future corruption is detected early.
Ensure the Keycloak Postgres has a working backup (relates to the SonarQube-backups gap in a separate issue — worth auditing all DB backup jobs).

Filed proactively by automated swarm health check (docker service log audit).

## Summary The Keycloak PostgreSQL database has a **corrupted TOAST value** in `pg_toast_2619` (the TOAST table backing the `pg_statistic` planner-statistics catalog). Every query whose plan needs the affected column statistics fails, and Keycloak's `ClearExpiredEvents` scheduled job fails on every run — so **expired Keycloak events are no longer being purged** (unbounded `EVENT_ENTITY` growth). ## Symptoms Postgres side (`keycloak_postgres-keycloak`, currently on dreamstream4), recurring ~every 60s: ``` ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619 ``` Keycloak side (`keycloak_keycloak`), ~every 7.5 min: ``` ERROR [org.keycloak.services.scheduled.ScheduledTaskRunner] Failed to run scheduled task ClearExpiredEvents: org.hibernate.exception.GenericJDBCException: ... [ERROR: missing chunk number 0 for toast value 131293 in pg_toast_2619] ``` Scope observed over 60 min: a single toast value (`131293`), single toast table (`pg_toast_2619` = `pg_statistic`), single failing task (`ClearExpiredEvents`). ## What this means `pg_toast_2619` is the TOAST relation for the system catalog `pg_statistic`. The corruption is in **cached planner statistics**, not in real Keycloak user/realm data — which makes it safe to remediate by regenerating statistics. But the planner keeps reading the dead toast pointer, so any statement that touches those stats errors out until the stale row is cleared. ## Impact - `ClearExpiredEvents` never completes -> `EVENT_ENTITY` grows without bound (DB bloat over time). - Any planner path needing the corrupt column stats can fail intermittently. ## Proposed remediation (take a backup first) Because the corruption is confined to `pg_statistic`, regenerating statistics clears it: ```sql -- as a superuser, against the keycloak DB -- (optional targeted: ANALYZE each table until the erroring one is found, which overwrites its stat rows) -- reliable blanket fix: DELETE FROM pg_statistic; -- may require: SET allow_system_table_mods = on; ANALYZE; -- regenerates all statistics from scratch ``` Then confirm the `missing chunk` errors stop and `ClearExpiredEvents` succeeds. ## Root-cause follow-up TOAST corruption usually traces to an unclean stop or a storage-layer problem: - Check dmesg / SMART on **dreamstream4** (and wherever this PG volume has run) for disk errors. - Verify whether `data_checksums` is enabled (`SHOW data_checksums;`) — if `off`, consider enabling on the next rebuild so future corruption is detected early. - Ensure the Keycloak Postgres has a working backup (relates to the SonarQube-backups gap in a separate issue — worth auditing all DB backup jobs). --- _Filed proactively by automated swarm health check (docker service log audit)._

spikerj commented

2026-07-12 21:31:31 +00:00

Recovery runbook up: infra PR #49 → docs/keycloak-postgres-toast-recovery.md. Summary of the approach (full detail + exact commands in the doc):

Blast-radius gate: SELECT count(*) FROM pg_statistic (reproduces) vs SELECT count(*) FROM event_entity (must be clean). If application tables ALSO throw toast errors → STOP, basebackup, escalate — that would be data corruption, not this runbook.
Fix (~2 min, Keycloak stays up): optional row-by-row identifier for a targeted DELETE FROM pg_statistic WHERE ctid=..., or the equally-safe DELETE FROM pg_statistic; — it's planner-stats cache, fully regenerated by the following VACUUM pg_statistic; ANALYZE;.
Verify: the ~60s missing chunk stream → 0; next ClearExpiredEvents (≤7.5 min) completes; event_entity count shrinks once the purge lands.
Root-cause tail: SHOW data_checksums; (if off, consider checksums at next maintenance — silent-corruption insurance on consumer hardware) + the #503 bad-RAM connection: if this recurs on dreamstream4, memtest it before trusting it with the identity DB.

Will close on your confirmation of step 4's three green checks.

Recovery runbook up: **infra PR #49** → `docs/keycloak-postgres-toast-recovery.md`. Summary of the approach (full detail + exact commands in the doc): 1. **Blast-radius gate**: `SELECT count(*) FROM pg_statistic` (reproduces) vs `SELECT count(*) FROM event_entity` (must be clean). If application tables ALSO throw toast errors → STOP, basebackup, escalate — that would be data corruption, not this runbook. 2. **Fix** (~2 min, Keycloak stays up): optional row-by-row identifier for a targeted `DELETE FROM pg_statistic WHERE ctid=...`, or the equally-safe `DELETE FROM pg_statistic;` — it's planner-stats cache, fully regenerated by the following `VACUUM pg_statistic; ANALYZE;`. 3. **Verify**: the ~60s `missing chunk` stream → 0; next `ClearExpiredEvents` (≤7.5 min) completes; `event_entity` count shrinks once the purge lands. 4. **Root-cause tail**: `SHOW data_checksums;` (if off, consider checksums at next maintenance — silent-corruption insurance on consumer hardware) + the #503 bad-RAM connection: if this recurs on dreamstream4, memtest it before trusting it with the identity DB. Will close on your confirmation of step 4's three green checks.

spikerj referenced this issue

2026-07-20 23:17:03 +00:00

EPIC: Angular 22 + Nx 23 full-leverage modernization (signal forms, resource APIs, affected CI, remote cache) #722

spikerj commented

2026-07-22 02:58:15 +00:00

Board-sweep status (2026-07-22): recovery RUNBOOK merged (infra #49). REMAINING: the actual TOAST repair + ClearExpiredEvents verification — an unexecuted ops procedure.

spikerj referenced this issue

2026-07-25 22:30:21 +00:00

CI: prune artpipe container pin tags — keep :latest + 3, they accumulate forever #850

spikerj commented

2026-07-29 15:49:05 +00:00

Audited against origin/master — the runbook shipped; the repair was never run. This ticket is marked resolved by association with infra PR #49, but that PR merged the documentation, not the fix.

Merged: docs/keycloak-postgres-toast-recovery.md (8ee2a8d, PR #49) — blast-radius gate, DELETE FROM pg_statistic; VACUUM; ANALYZE, verification steps, and the data_checksums root-cause tail.

Not run. The direct evidence is #561, filed 2026-07-14 and still open, which reports the identical error — missing chunk number 0 for toast value 131293 in pg_toast_2619 — still recurring every 15-minute cycle after this ticket's remediation was written, and states plainly that the repair was never executed. Corroborating: git grep -rn 'pg_statistic|toast' origin/master outside the runbook finds nothing — no execution record, no post-repair note anywhere in any repo.

So the state is: we know exactly how to fix this, we wrote it down, and the corruption is still live.

Remaining:

Execute the runbook against the Keycloak database.
Confirm the ~60-second error stream stops.
Confirm ClearExpiredEvents completes and EVENT_ENTITY actually drains.
Run the SHOW data_checksums; tail to settle the root cause — worth doing, because #503 established that this host has confirmed bad RAM, which is a plausible common cause for page-level corruption.

Given #561 tracks the same live corruption, one of these two should probably absorb the other once the repair runs.

Audited against `origin/master` — **the runbook shipped; the repair was never run.** This ticket is marked resolved by association with infra PR #49, but that PR merged the *documentation*, not the fix. **Merged:** `docs/keycloak-postgres-toast-recovery.md` (`8ee2a8d`, PR #49) — blast-radius gate, `DELETE FROM pg_statistic; VACUUM; ANALYZE`, verification steps, and the `data_checksums` root-cause tail. **Not run.** The direct evidence is **#561**, filed 2026-07-14 and still open, which reports the *identical* error — `missing chunk number 0 for toast value 131293 in pg_toast_2619` — still recurring every 15-minute cycle **after** this ticket's remediation was written, and states plainly that the repair was never executed. Corroborating: `git grep -rn 'pg_statistic|toast' origin/master` outside the runbook finds nothing — no execution record, no post-repair note anywhere in any repo. So the state is: we know exactly how to fix this, we wrote it down, and the corruption is still live. **Remaining:** 1. Execute the runbook against the Keycloak database. 2. Confirm the ~60-second error stream stops. 3. Confirm `ClearExpiredEvents` completes and `EVENT_ENTITY` actually drains. 4. Run the `SHOW data_checksums;` tail to settle the root cause — worth doing, because #503 established that this host has confirmed bad RAM, which is a plausible common cause for page-level corruption. Given #561 tracks the same live corruption, one of these two should probably absorb the other once the repair runs.

spikerj referenced this issue

2026-07-29 15:52:50 +00:00

[Bug][Infra][CRITICAL] SERVER node crash-looping — kernel 'BUG: Bad page state in process dockerd' (memory corruption / likely bad RAM); flaps all SERVER-pinned services #503

spikerj referenced this issue

2026-07-29 15:56:17 +00:00

[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

spikerj referenced this issue

2026-07-29 17:08:14 +00:00

[Infra][SonarQube] CE fails extracting LARGE uploaded reports: PGbytea ArrayIndexOutOfBoundsException reading ce_task_input (root cause of the old #344 crash) #642

Sign in to join this conversation.