Lewati ke isi

T4 W05 Combined-Session Unique Index Fix

Label: T4 W05 COMBINED-SESSION UNIQ FIX
When: 2026-08-12 (server /home/scola/odoo)
Code: scola_timetable 17.0.1.0.5 — models/session.py _ensure_session_uniqueness_indexes + migrations/17.0.1.0.5/post-migrate.py
This step: live DDL alignment on remaining tenants (no app-code change)

Root cause

Combined sessions intentionally create one op.session row per batch with the same (faculty_id, start_datetime), linked by combined_session_key.

Legacy index op_session_faculty_start_uniq was:

UNIQUE (faculty_id, start_datetime)
WHERE faculty_id IS NOT NULL AND start_datetime IS NOT NULL

That blocked sibling rows for multi-batch combined sessions (PostgreSQL unique violation) even when application conflict checks allowed them.

Fix (desired indexes)

  1. op_session_faculty_start_uniq — unique on (faculty_id, start_datetime) only for non-combined rows:
WHERE faculty_id IS NOT NULL
  AND start_datetime IS NOT NULL
  AND (combined_session_key IS NULL OR btrim(combined_session_key) = '')
  1. op_session_combined_batch_uniq — unique on (combined_session_key, batch_id) for non-empty keys.

Apply pattern: SAVEPOINT → DROP INDEX IF EXISTS op_session_faculty_start_uniq → CREATE UNIQUE INDEX … → CREATE UNIQUE INDEX IF NOT EXISTS op_session_combined_batch_uniq → release; on duplicate failure roll back savepoint (do not force-delete without analysis).

Tenant audit & apply (2026-08-12)

DB Before Action After
scola_goldenbabyntree (GBN / prod) already relaxed + combined none OK
scoladev_demo already relaxed + combined none OK
scola_aljabbar coarse faculty uniq only DROP+CREATE via savepoint OK
scola_rejis coarse DROP+CREATE OK
scola_sdn1pgt coarse DROP+CREATE OK
scola_spansa coarse DROP+CREATE OK
scola_golden coarse DROP+CREATE OK
scola_test DB not present skipped n/a

Precheck on the five updated DBs: non_combined_faculty_dups=0, combined_batch_dups=0, combined_rows=0 — CREATE succeeded on all.

Method used: raw SQL matching session.py (same savepoint semantics). Optional later: -u scola_timetable to bump ir_module_module from 17.0.1.0.4 → 17.0.1.0.5 (post-migrate is idempotent). At audit time all listed DBs still reported installed version 17.0.1.0.4 even where DDL was already correct.

GBN confirmation

scola_goldenbabyntree still has both indexes with combined_session_key predicates (relaxed faculty uniq + combined batch uniq).

Orphan caveat (GBN Literacy P-4 / P-5)

While the coarse unique was in force, multi-batch combined creates could fail after writing only part of the group. On GBN:

  • All 402 non-empty combined_session_key values are singleton keys (n=1 row per key; 0 multi-batch keys).
  • Literacy on P-5: 70 sessions carry a combined key but have no sibling batch row under that key; P-4 Literacy has 49 sessions with no combined key; no shared (faculty_id, start_datetime) across P-4/P-5 Literacy today.

Ops remaining (manual product): recreate intended Literacy (and any other) combined sessions for P-4/P-5 so each key spans the real batch set, or clear stale singleton keys if they were aborted creates. Index fix alone does not repair those rows.

  • Prior coarse-dup residual (different issue): T4-W05-session-uniq-residual-closure.md
  • Rollout: T4-W05-rollout.md