getPracticasFilterOptions jtps subquery: push comisión predicate into a SECURITY INVOKER get_comision_picked_teachers(comisionIds[]) fn before the org-wide pick materialization (MARS-550/560 residual, 80ms->~20ms, fixes unbounded-growth-with-practicas)
dbperf snapshot 2026-07-26: getPracticasFilterOptions bundle residual = 81ms/call, ~93% in the jtps subquery via vComisionPickedTeachers. Root cause (db-mars-cc EXPLAIN ANALYZE): the view materializes the ENTIRE org-wide pick universe (full practicas seq scan + users joined twice for all picks) then discards ~94% via the caller's comision outer-join AFTERWARD; the comisionId predicate can't push in because picks is a multiply-referenced CTE (auto-materialize fence). Cost scales with total practicas, worsens as DB grows -> not benign. Fix: SECURITY INVOKER fn replicating the view body but pushing comisionId=ANY(p_comision_ids) onto BOTH the practicas scan and sa_fallback anti-join before DISTINCT; coder repoints the jtps subquery to call it with caller comisionIds. Byte-parity to (view filtered to those comisiones) proven in-migration. View stays for unscoped consumers. DDL -> audit pre-apply review. canViewPractica auth gate untouched; comision-scoping is UX-narrowing not security containment.
Questions
Activity
-
--body
-
jtps-subquery repointed to scoped get_comision_picked_teachers() fn (mig 091, SECURITY INVOKER + three-way REVOKE, parity 0,0). Subquery 105ms->14ms, filter-options bundle ~113->~20ms; fixes unbounded-growth-with-total-practicas. SHA 872495a / v2.58.6 live. Pre-apply audit waived (audit-mars-ca offline), retro cross-vendor review queued.