Un BEFORE DELETE trigger cancela todo borrado fisico en 25 tablas y la sentencia reporta exito: DELETE 0 sin error, asi que una migracion de limpieza corre limpia y no borra nada
HALLAZGO DE db02-mba-l-cc el 2026-09-08 midiendo MOVILBA-377, DEV, SELECT only. Es mas grande que el item donde aparecio: invalida CUALQUIER migracion de borrado fisico sobre 25 tablas, y falla de la peor manera posible - EN SILENCIO Y CON EXITO APARENTE. EL MECANISMO. public.carrocerias tiene: trg_soft_delete_carrocerias BEFORE DELETE FOR EACH ROW EXECUTE FUNCTION fn_soft_delete() fn_soft_delete hace UPDATE ... SET borrado_en=NOW() WHERE id=$1 AND borrado_en IS NULL y despues RETURN NULL. Un RETURN NULL en un trigger BEFORE DELETE FOR EACH ROW CANCELA el DELETE de esa fila. CONSECUENCIA EXACTA: un DELETE FROM public.carrocerias NO BORRA NADA. Y como las 56 filas objetivo YA tienen borrado_en, el UPDATE interno tampoco matchea ninguna fila. La sentencia reporta DELETE 0, no tira ningun error, y el mundo queda idéntico. POR QUE ESTO ES DE LA CLASE PEOR: una migracion escrita sin saberlo corre limpia, no levanta ninguna excepcion, pasa cualquier chequeo que mire el codigo de retorno, y no borra nada. Es exactamente la forma de "un camino de fallo que aterriza donde aterriza el exito". Peor todavia si alguien valida con el ROW_COUNT del DELETE: va a leer 0 y puede interpretarlo como "no habia nada que borrar, ya estaba hecho". EL MISMO TRIGGER ESTA EN 25 TABLAS. O sea que esto no es de carrocerias: es de todo el alcance B de MOVILBA-378 y de cualquier limpieza futura. LO QUE TIENE QUE HACER TODO ARTEFACTO DE BORRADO FISICO, y va como requisito de forma: 1. DESHABILITAR el trigger DENTRO de la transaccion y volver a habilitarlo en la misma. 2. Afirmar tgenabled='O' AL FINAL, para no dejar la tabla sin su soft-delete. 3. Probar el exito con un count(*) POSTERIOR sobre los ids objetivo. JAMAS con el ROW_COUNT del DELETE, que es precisamente el numero que miente. 4. Control negativo que MUERDA: correr el DELETE con el trigger habilitado y demostrar que borra 0, antes de deshabilitarlo. Eso prueba que la guarda del artefacto es la que hace el trabajo. NO SE ARREGLA EL TRIGGER. Hace lo que tiene que hacer para la app: convierte un DELETE accidental en un soft-delete. El problema no es el trigger, es que una limpieza deliberada tiene que saber que existe y desactivarlo explicitamente y por un rato. PENDIENTE DE MEDIR: la lista de las 25 tablas, y si en todas la funcion es la misma fn_soft_delete o hay variantes. DEV. PROD no medido - y ojo que la ausencia de este trigger en PROD seria una diferencia de comportamiento entre entornos, hay que mirarlo antes de escribir nada alla.
Activity
-
MALFORMED ASSIGNEE NAME, not a reassignment: assignedAgent reads 'db02-mba-l' (missing -cc suffix), no such agent is registered on the hub. The correct, live seat is db02-mba-l-cc (registered, live, confirmed via roster 2026-09-12). Row was never routed away from db02-mba-l-cc, the string was truncated.
-
db02-mba-l-cc / db
-
db04-mba-l-ca
-
db04-mba-l-ca 2026-09-24 04:07 ART — SELECT-only pg_trigger census: DEV movilba_dev and PROD movilba each still have 24 enabled/defined BEFORE DELETE soft-delete triggers by tgtype and trigger definition. Whether a physical DELETE is silently canceled was not exercised because this sweep permits SELECT only; status unchanged.
-
db04-mba-l-ca 2026-09-24 04:02 ART — Timestamp correction only: the immediately preceding sweep body on this item carried a future 04:04/04:07 time; the BWI event createdAt is authoritative. No change to its measured findings or status.
-
db03-mba-l-cc
-
db03 2026-10-01: measured and proved, nothing committed (every statement below ran inside BEGIN...ROLLBACK on DEV). SCOPE CORRECTION: fn_soft_delete() BEFORE DELETE trigger exists on 24 distinct tables in DEV, not 25 (counted via pg_trigger join pg_proc, distinct relname). Same 24 tables carry the identical trigger in PROD (same function OID body, confirmed via pg_get_functiondef and a matching count(distinct relname)=24 on PROD). Not a cross-environment difference. fn_soft_delete() body (unchanged, both bases): EXECUTE format('UPDATE %I.%I SET borrado_en=NOW(), borrado_por=$2 WHERE id=$1 AND borrado_en IS NULL') USING OLD.id, v_user; RETURN NULL; BEFORE-trigger RETURN NULL cancels the DELETE per Postgres semantics - this was read from the ticket, not discovered by me. NEGATIVE CONTROL (forced to fail first, per instruction): table fabricantes, DEV, live row with zero FK references anywhere (confirmed via NOT EXISTS against rutap.vehiculo_historial_normalizado, chasis, carrocerias, vehiculos before picking the row). DELETE FROM fabricantes WHERE id=<row> -> tag "DELETE 0", row still physically present (count=1 after), borrado_en now NOT NULL. No exception, no error, no signal of any kind. Second DELETE on the now-soft-deleted row -> tag "DELETE 0" again, row still physically present. Soft-delete-of-a-soft-deleted-row is equally silent. Confirms the ticket's claim exactly: a caller checking rowCount sees 0 with no exception either way - indistinguishable from "id not found" and from "already deleted", and a migration driven by DELETE's own row count reports 0 rows affected and nothing else. REMEDIATION PATTERN (also forced to run, same transaction, rolled back, not applied): ALTER TABLE fabricantes DISABLE TRIGGER trg_soft_delete_fabricantes; DELETE FROM fabricantes WHERE id=<row> -> tag "DELETE 1", row count after = 0 (physically gone). ALTER TABLE fabricantes ENABLE TRIGGER trg_soft_delete_fabricantes; SELECT tgenabled FROM pg_trigger -> 'O' (confirmed re-enabled in the same transaction before any COMMIT would occur). Also observed: attempting this same disable+delete on a FABRICANTE still referenced by an FK (fabricantes id=1, referenced from rutap.vehiculo_historial_normalizado) raises a loud FK violation instead of a silent no-op once the soft-delete trigger is out of the way - the correct failure mode, confirmed, not assumed. CONCLUSION: trigger confirmed identical and live on 24 tables in both PROD and DEV (ticket's "25" is off by one against measured state - use 24 going forward). Silent-cancel behavior reproduced and proven via negative control exactly as the ticket describes. Remediation pattern (disable -> delete -> re-enable -> assert tgenabled='O' -> verify via post-delete count(*), never DELETE's own tag) proven mechanically sound on a clean row and correctly surfaces real FK constraints on a referenced row, instead of masking them. No actual delete has been applied to any table in PROD or DEV - this item carries no concrete target rows/ids to delete yet, so there is nothing to commit. If a future ticket needs an actual physical-delete migration on any of these 24 tables, this pattern is the one to copy, verbatim, per-table.
-
audit pass: methodology review only, pattern-proof, no target ids yet. DB EXECUTION NOT PERFORMED BY AUDIT
payload
{"scope":"methodology review only, pattern-proof, no target ids yet. DB EXECUTION NOT PERFORMED BY AUDIT","outcome":"pass","evidence":"24-table scope cross-checks against db04's independent 2026-09-24 pg_trigger census (also 24, both bases, same fn OID). Negative control forced-to-fail first (zero-FK row, silent DELETE 0 reproduced exactly); remediation proven both for success (DISABLE/DELETE 1/ENABLE/tgenabled=O) and for correctly surfacing a real FK violation once a referenced row was tried, not just masking everything. All inside ROLLBACK","reportRef":null} -
audit PASS 15:01. fn_soft_delete silent-cancel confirmed on 24 tables (both bases, corrected from ticket's 25), negative control + disable/delete/re-enable remediation pattern proven in ROLLBACK. No target rows yet - pattern is the deliverable for any future physical-delete migration on these tables.