v_snapshot_catalog_preflight tiene su propia copia del join de fabricante que MOVILBA-722 parcheo solo en el proyector, asi que marca filas que el proyector ya resuelve

Sub-tickets

Activity

• coder03-mba-l-cc created · 1w ago
• db04-mba-l-ca statusChanged · 1w ago
  PROD-first read-only baseline and installed-object copy census underway; no DDL applied.
• db04-mba-l-ca progress · 1w ago
  SELECT-only PROD 17:13 ART: current_database=movilba, snapshot 11, preflight 1 group/1 row: motor_fabricante CUMMINS 6 CIL, AH992CY. History row (11,5560) has raw_motor=CUMMINS 6 CIL, motor_fabricante_id=24/CUMMINS, normalizacion_estado=resuelto: false positive 1, genuine unresolved 0. DEV current_database=movilba_dev snapshot 21 repeats 1/1 and row (21,5560) resolves identically; genuine unresolved 0. Installed-object census across non-system pg_proc + views/matviews: legacy MERCEDEZ manufacturer predicate occurs only in rutap.proyectar_snapshot(bigint) and rutap.v_snapshot_catalog_preflight; the projector alone has the 722 CUMMINS alias. No third installed routine/view copy. Runtime src/tools search found no third matching predicate; historical migration files have old copies, not installed readers. Viewdef md5 PROD 0ba1205392b48fe0a5efd510174f35e9, DEV f742cfe55f59298552d48a8e73d76a5b; owners movilba_owner/movilba_user, relacl NULL both. No DDL applied.
• db04-mba-l-ca progress · 1w ago
  Guarded view-only draft /data-lezama/movilba/evidencia/MOVILBA-746/2026-10-08-preflight-cummins-alias-PROD-Y-DEV.sql SHA256 f21aaeacb4c706cad38586a0a5510c1252c93f1d1c938c98b67155e872d02af6 sent to audit. PROD-first then DEV ROLLBACK rehearsals: preflight 1 -> 0 in each, owner/ACL/5 projected columns retained; post-rollback viewdef md5 restored to 0ba12053 PROD and f742cfe5 DEV, baseline 1 restored. Default is ROLLBACK; explicit movilba746_apply=true commits. No live DDL applied pending audit PASS.
• db04-mba-l-ca progress · 1w ago
  2026-10-08 18:11 ART status after temporary MOVILBA-367 predicate check: current pinned snapshots have exactly ONE preflight issue in PROD (11) and DEV (21), AH992CY/fila 5560 motor_fabricante='CUMMINS 6 CIL'. In both, projected history resolves motor_fabricante_id=24 CUMMINS with estado=resuelto, so 1/1 is false positive from the stale preflight join; genuine unresolved=0. Installed-object census found only two live copies of this manufacturer match: proyector (722-fixed) and v_snapshot_catalog_preflight (stale); no third installed routine/view. View-only SQL f21aaeac...d02af6 rehearsed under ROLLBACK PROD then DEV (1->0; rollback restored 1), sent to audit, no live DDL. 367 was a short read-only interruption; 746 awaits audit verdict before apply.
• db04-mba-l-ca blocked · 1w ago
  PROD and DEV rollback rehearsals passed, but live apply awaits audit verdict on view-only SQL SHA f21aaeacb4c706cad38586a0a5510c1252c93f1d1c938c98b67155e872d02af6; no DB write authorized before PASS.
• audit-mba-l-cc audit-finding · 1w ago
  # MOVILBA-746 @ f21aaeac — audit verdict: PASS. Root cause left standing is filed as MOVILBA-766 audit-mba-l-cc. Static review of the SQL plus my own SELECT-only measurements in BOTH bases. I ran no leg of this file. /data-lezama/movilba/evidencia/MOVILBA-746/2026-10-08-preflight-cummins-alias-PROD-Y-DEV.sql f21aaeacb4c706cad38586a0a5510c1252c93f1d1c938c98b67155e872d02af6, 113 lines re-hashed by me at verdict time; equals the SHA on the item ## 1. The fix makes the two predicates term-for-term equal — measured, not accepted MOVILBA-722's projector join (`docs/migrations/2026-10-06-movilba-722-...sql:18`): ON upper(mf.fabricante) = CASE WHEN upper(btrim(s.motor)) = 'CUMMINS 6 CIL' THEN 'CUMMINS' ELSE replace(upper(btrim(s.motor)),'MERCEDEZ','MERCEDES') END AND mf.borrado_en IS NULL The diagnostic after this file's replace, read from the INSTALLED PROD body (`:66`, `:162-164`): motor_n = upper(btrim(COALESCE(r.motor, ''))) upper(f.fabricante) = CASE WHEN s.motor_n = 'CUMMINS 6 CIL' THEN 'CUMMINS' ELSE replace(s.motor_n,'MERCEDEZ','MERCEDES') END AND f.borrado_en IS NULL Identical given that substitution. The only textual difference is `COALESCE(...,'')`, which cannot change the result: the branch is already gated by `s.motor_n <> ''` at `:162`. ## 2. Both pins verified by me, in both bases, and the surgery has exactly one target | base | server | installed viewdef md5 | file's pin | occurrences of the replaced substring | |---|---|---|---|---| | `movilba` | 17.6 | `0ba1205392b48fe0a5efd510174f35e9` | `:36` same | **1** | | `movilba_dev` | 15.17 | `f742cfe55f59298552d48a8e73d76a5b` | `:39` same | **1** | Two different pins for the same logical view is CORRECT here, not drift: the two servers deparse differently. **Because the replace has exactly one hit in each base, the other 11 UNION ALL branches come out byte-identical** — that is why I do not treat them as newly unvalidated text. `:48-51` asserts snapshot row count 5558; measured in PROD, `snapshot_id = 11` has exactly 5558 rows. ## 3. The post-state 0 is derived independently, not quoted from the rehearsal `$post$` at `:85-88` demands `count(*) = 0`. The reason it holds: `fabricantes` id **24** is `CUMMINS` with `borrado_en IS NULL` in **both** bases, so the new CASE arm makes the `NOT EXISTS` false for `CUMMINS 6 CIL` and that row leaves the view. Measured by me in both bases. DEV's baseline row matches the guard's asserted shape exactly, read by me: `21 | motor_fabricante | CUMMINS 6 CIL | 1 | {AH992CY}`. ## 4. The controls are real controls - `$guard$` `:43-47` aborts on view-body drift per base; `:52-60` aborts unless the pre-state is **exactly one row with that exact value, count and domain array**. So the file asserts a 1 → 0 delta on a named row, not merely "zero at the end". - `:70-73` computes the occurrence count of the substring it is about to replace and raises unless it is exactly 1. A blind `replace()` would silently no-op on a changed body; this one cannot. - `$post$` `:89-101` re-reads `relowner`, `relacl` and the ordered column list and compares against the `m746_before` snapshot taken at `:15-24`. `CREATE OR REPLACE VIEW` preserves grants by construction, so this is belt-and-braces rather than the load-bearing part — the load-bearing part is that it is a REPLACE and not a `DROP VIEW`, which is what would have discarded PROD's non-NULL `relacl`. - Default is ROLLBACK (`:6-9`, `:108-112`); apply is `-v movilba746_apply=true`. ## 5. Root cause NOT fixed by this file — filed as MOVILBA-766 746's title is right: the predicate lives in two objects. This file aligns the second copy **by hand**. After it there is no live divergence, but the next alias added to the projector reappears as a false positive in the diagnostic, silently and in the over-reporting direction. The durable fix is one shared `IMMUTABLE` function called by both. Filed as MOVILBA-766 with the two constraints that make it non-trivial (the projector is text-pinned by other migrations including MOVILBA-367's preflight, and the diagnostic's body md5 is pinned per base). **Not a condition of this PASS.** ## 6. Operational note for the apply `$guard$` and `$post$` both SELECT from `rutap.v_snapshot_catalog_preflight`. In PROD, `has_table_privilege('svc_nextjs', 'rutap.v_snapshot_catalog_preflight', 'select')` is **false** — measured. The apply must run as the same role that rehearsed, or it aborts in the guard. ## 7. NOT EXERCISED — part of the verdict - **I ran no leg**, in either base, apply or ROLLBACK. db04's two rehearsals remain their measurement. - **PROD's baseline ROW is unreadable to me** (the privilege above), so "PROD has exactly 1 issue, AH992CY" stays db04's measurement. What I verified in PROD myself: the viewdef pin, the single substring hit, snapshot 11 at 5558 rows, and `fabricantes` 24 live. - **MOVILBA-611's `EXCEPT ALL` positive control is not re-run inside this file.** It proves the named row goes 1 → 0 and the global count is 0; it does not re-demonstrate that the other 11 branches still fire. Byte-identical text (§2) is the argument that they do, which is a derivation, not a re-measurement. - Multi-snapshot behaviour is unexercised: one snapshot per base (11 PROD, 21 DEV). - No `fabricantes` alias other than `CUMMINS 6 CIL` and `MERCEDEZ` was searched for, in the book or in the view. If another exists, this file does not address it and the guard's `count(*) = 1` would abort rather than mis-apply.
• audit-mba-l-cc audited · 1w ago
  audit pass: SQL f21aaeac (113 lines, re-hashed by me), its guard/replace/post blocks and the resulting predicate against MOVILBA-722's projector join. Pins, substring hit count, snapshot size and the CUMMINS row verified by me in BOTH bases. NOT run by me; PROD's baseline row unreadable to svc_nextjs.
  payload

  {"scope":"SQL f21aaeac (113 lines, re-hashed by me), its guard/replace/post blocks and the resulting predicate against MOVILBA-722's projector join. Pins, substring hit count, snapshot size and the CUMMINS row verified by me in BOTH bases. NOT run by me; PROD's baseline row unreadable to svc_nextjs.","outcome":"pass","evidence":"PASS on f21aaeac, 113 lines, re-hashed by me; full body in the audit-finding.\n\nPREDICATES NOW TERM FOR TERM EQUAL, measured not accepted. 722's projector join (its migration :18) wraps upper(btrim(s.motor)) in the CUMMINS-6-CIL CASE over the MERCEDEZ replace, with borrado_en IS NULL. In the INSTALLED PROD view body, motor_n is upper(btrim(COALESCE(r.motor,''))) at :66 and the motor_fabricante branch at :162-164 has that shape, so after the replace the two are identical. The only difference is the COALESCE, gated out by motor_n <> ''.\n\nBOTH PINS VERIFIED BY ME: movilba 17.6 viewdef md5 0ba12053 = the :36 pin, movilba_dev 15.17 f742cfe5 = :39, and the replaced substring occurs EXACTLY ONCE in each. Two pins for one view is correct, not drift - the majors deparse differently. One hit means the other 11 UNION ALL branches come out byte-identical. PROD snapshot 11 has exactly 5558 rows, as :48-51 asserts.\n\nPOST-STATE 0 IS DERIVED, NOT QUOTED: fabricantes id 24 is CUMMINS with borrado_en NULL in BOTH bases, so the new arm makes the NOT EXISTS false and the row leaves the view. DEV's baseline row read by me matches the guard: 21, motor_fabricante, CUMMINS 6 CIL, 1, {AH992CY}.\n\nCONTROLS: the guard aborts unless the pre-state is that one row with that count and domains, so the assertion is a 1-to-0 delta on a NAMED row; :70-73 raises unless the substring count is 1; REPLACE, not DROP VIEW, so PROD's relacl survives.\n\nROOT CAUSE NOT FIXED, FILED AS MOVILBA-766, not a condition of this PASS: the predicate still lives in two objects, so the next alias added to the projector reappears as a silent over-report in the diagnostic.\n\nAPPLY NOTE: guard and post both SELECT the view, which svc_nextjs cannot read in PROD - apply as the role that rehearsed.\n\nNOT EXERCISED: no leg run by me, db04's rehearsals stay theirs; PROD's baseline row unreadable to me; 611's EXCEPT ALL not re-run, so the other 11 branches rest on byte-identity; one snapshot per base; no other alias searched.","reportRef":null}

• db04-mba-l-ca note · 1w ago
  Resolved by applied MOVILBA-766 migration SHA256 d17e87cd72c861598c059b0bbe8cd18de974fe70ccca9b0c5bb75b0750c526d3, PROD then DEV. This installed the shared normalizar_clave_fabricante helper in both the projector and preflight, including the CUMMINS 6 CIL -> CUMMINS arm. Post-apply preflight issue count for AH992CY/CUMMINS 6 CIL is 0 in both bases; independent post-COMMIT view/body pins recorded on MOVILBA-766. The separately rehearsed 746 SQL f21aaeac was not applied and must not be applied; its behavior is included in 766.
• db04-mba-l-ca unblocked · 1w ago
  cleared: PROD and DEV rollback rehearsals passed, but live apply awaits audit verdict on view-only SQL SHA f21aaeacb4c706cad38586a0a5510c1252c93f1d1c938c98b67155e872d02af6; no DB write authorized before PASS.
• db04-mba-l-ca completed · 1w ago
  Fixed by applied MOVILBA-766 SHA d17e87cd72c861598c059b0bbe8cd18de974fe70ccca9b0c5bb75b0750c526d3; do not separately apply f21aaeac.