103 lines
4 KiB
PL/PgSQL
103 lines
4 KiB
PL/PgSQL
-- Migration 131: fix NumericValueOutOfRange crash in record_listing_price_change()
|
||
--
|
||
-- Incident: prod run_id=260, 2026-06-20
|
||
-- A listing scrubbed by avito_full_load had an OLD.price_rub that was
|
||
-- garbage-small (data entry error or placeholder value). The computed
|
||
-- diff_percent = round((NEW-OLD)/OLD*100, 2) exceeded ±999.99, which
|
||
-- overflows numeric(5,2) (precision=5, scale=2, max value=±999.99).
|
||
-- PostgreSQL raised DataError: "numeric field overflow" inside the
|
||
-- trigger, which caused the INSERT into listings to abort, which caused
|
||
-- the entire avito_full_load batch to crash.
|
||
-- The trigger is shared across all non-cian sources → same crash would
|
||
-- occur for yandex/domclick full_load batches on similar data.
|
||
--
|
||
-- Fix (two parts):
|
||
-- 1. Widen diff_percent column to numeric(8,2) (max ±999999.99%).
|
||
-- Widening precision is safe and lossless for existing data.
|
||
-- 2. Replace the trigger function to clamp the computed value into
|
||
-- [−999999.99, +999999.99] so even future extreme deltas cannot
|
||
-- overflow the column. All other logic is preserved exactly as in
|
||
-- migration 116.
|
||
--
|
||
-- Dependencies: offer_price_history table (023), listings table,
|
||
-- offer_price_history_listing_change_uq unique constraint,
|
||
-- trigger listings_price_change_trg (116).
|
||
--
|
||
-- Idempotent: ALTER TYPE widening is a no-op if already numeric(8,2);
|
||
-- CREATE OR REPLACE FUNCTION is always safe to re-run.
|
||
-- Trigger is NOT recreated — the existing trigger already points to
|
||
-- record_listing_price_change(); replacing the function body is enough.
|
||
--
|
||
-- Deploy order: standalone; auto-applied by deploy pipeline via
|
||
-- _schema_migrations. Do NOT apply manually on prod — causes drift.
|
||
|
||
BEGIN;
|
||
|
||
-- Part 1: widen the column.
|
||
-- numeric(5,2) → numeric(8,2): raises max from ±999.99 to ±999999.99.
|
||
-- PostgreSQL widens precision without rewriting rows (catalogue-only change
|
||
-- for heap tables when scale does not decrease and precision does not decrease).
|
||
ALTER TABLE offer_price_history
|
||
ALTER COLUMN diff_percent TYPE numeric(8,2);
|
||
|
||
-- Part 2: replace trigger function with clamped diff_percent computation.
|
||
-- All guards from migration 116 are preserved verbatim:
|
||
-- • same-value skip (IS NOT DISTINCT FROM)
|
||
-- • NULL new-price skip
|
||
-- • cian source skip (backfill exclusion — see 116 for rationale)
|
||
-- • OLD.price_rub > 0 gate (avoid division by zero / nonsensical pct)
|
||
-- • ON CONFLICT DO NOTHING (idempotent re-scrape)
|
||
CREATE OR REPLACE FUNCTION record_listing_price_change()
|
||
RETURNS trigger
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
-- Skip if price did not actually change (same-value upsert write).
|
||
IF NEW.price_rub IS NOT DISTINCT FROM OLD.price_rub THEN
|
||
RETURN NEW;
|
||
END IF;
|
||
-- Skip if the new price is NULL (defensive; price_rub is NOT NULL in practice).
|
||
IF NEW.price_rub IS NULL THEN
|
||
RETURN NEW;
|
||
END IF;
|
||
-- Skip cian: its history is source-provided via cian_price_history.py backfill.
|
||
-- That service selects listings WHERE oph.listing_id IS NULL (zero rows in
|
||
-- offer_price_history). Inserting a trigger row here would permanently
|
||
-- exclude those listings from the backfill, losing accurate historical timestamps.
|
||
IF NEW.source = 'cian' THEN
|
||
RETURN NEW;
|
||
END IF;
|
||
|
||
INSERT INTO offer_price_history (
|
||
listing_id,
|
||
change_time,
|
||
price_rub,
|
||
source,
|
||
diff_percent,
|
||
recorded_at
|
||
) VALUES (
|
||
NEW.id,
|
||
now(),
|
||
NEW.price_rub,
|
||
NEW.source,
|
||
CASE
|
||
WHEN OLD.price_rub IS NOT NULL AND OLD.price_rub > 0
|
||
THEN LEAST(GREATEST(
|
||
round(
|
||
(NEW.price_rub::numeric - OLD.price_rub::numeric)
|
||
/ OLD.price_rub::numeric * 100,
|
||
2
|
||
),
|
||
-999999.99), 999999.99)
|
||
ELSE NULL
|
||
END,
|
||
now()
|
||
)
|
||
ON CONFLICT ON CONSTRAINT offer_price_history_listing_change_uq
|
||
DO NOTHING;
|
||
|
||
RETURN NEW;
|
||
END;
|
||
$$;
|
||
|
||
COMMIT;
|