bot-backend
5d6238e89a
Prod deploy 2026-05-31 13:50 failed on 095: 'cannot drop column sale_type_text of table listings because view v_data_quality depends on it'. The view (recreated in 094) has a CTE 'SELECT * FROM listings' which records a column-level dependency on EVERY listings column incl sale_type_text — so the bare DROP COLUMN aborts the whole migration (exit 1), blocking 095/096/097 from applying. Fix: DROP VIEW v_data_quality -> DROP COLUMN sale_type_text -> recreate the view with the identical 094 DDL (sale_type_text was never an output column, only pulled in implicitly via SELECT *; after the drop, * no longer includes it). Verified v_data_quality is the only object doing SELECT * FROM listings — listings_search_mv and v_price_divergence reference explicit columns, so no other dependency blocks. 095 never applied on prod (transaction rolled back atomically, not in _schema_migrations) → next deploy re-runs the corrected file. Editing the merged migration in place is the correct recovery (a new 098 would not help: the runner hits 095 first and keeps failing). Refs #731
88 lines
5.5 KiB
PL/PgSQL
88 lines
5.5 KiB
PL/PgSQL
-- 095_dead_schema.sql
|
||
-- Purpose: Удаление мёртвой схемы (верифицированный безопасный подмножество аудита TradeinDb_Audit_May30).
|
||
--
|
||
-- Контекст (Issue #731):
|
||
-- sale_type_text: столбец 100% NULL в listings, code refs удалены в том же PR.
|
||
-- audit_log: 0 строк, 0 python refs, триггеры не зависят (верифицировано).
|
||
--
|
||
-- ⚠️ View-зависимость (фикс после падения деплоя 2026-05-31):
|
||
-- v_data_quality (recreated в 094) содержит CTE `SELECT * FROM listings`, что
|
||
-- фиксирует column-level зависимость на ВСЕ колонки listings, включая
|
||
-- sale_type_text — поэтому `DROP COLUMN sale_type_text` падал
|
||
-- ("cannot drop ... other objects depend on it"). Решение: drop view →
|
||
-- drop column → recreate view ТЕМ ЖЕ DDL (после дропа колонки `SELECT *`
|
||
-- её просто не включит). Проверено: только v_data_quality делает SELECT *
|
||
-- из listings; listings_search_mv / v_price_divergence берут явные колонки.
|
||
--
|
||
-- Idempotency:
|
||
-- DROP VIEW / DROP COLUMN IF EXISTS / DROP TABLE IF EXISTS / CREATE OR REPLACE —
|
||
-- безопасны при повторном запуске.
|
||
--
|
||
-- Явно НЕ удаляем: houses_price_dynamics, agents, sellers, estimate_photos,
|
||
-- house_reviews, listings_snapshots.
|
||
|
||
BEGIN;
|
||
|
||
-- #731: drop dead schema (verified-safe subset of TradeinDb_Audit_May30)
|
||
|
||
-- v_data_quality зависит от listings.sale_type_text через `SELECT * FROM listings`
|
||
-- (см. 094). Дропаем view перед дропом колонки, пересоздаём ниже тем же DDL.
|
||
DROP VIEW IF EXISTS v_data_quality;
|
||
|
||
-- sale_type_text: 100% NULL in listings, code refs removed in same PR (#731)
|
||
ALTER TABLE IF EXISTS listings DROP COLUMN IF EXISTS sale_type_text;
|
||
|
||
-- Recreate v_data_quality (DDL идентичен 094 — sale_type_text не в выводе, только
|
||
-- неявно через SELECT * CTE; после дропа колонки * её не включает).
|
||
CREATE OR REPLACE VIEW v_data_quality AS
|
||
WITH active_listings AS (
|
||
SELECT * FROM listings WHERE is_active = true
|
||
)
|
||
SELECT
|
||
(SELECT count(*) FROM houses) AS houses_total,
|
||
(SELECT count(*) FROM houses h
|
||
WHERE EXISTS (SELECT 1 FROM house_sources hs WHERE hs.house_id = h.id)) AS houses_with_source,
|
||
(SELECT count(*) FROM houses h
|
||
WHERE EXISTS (SELECT 1 FROM house_sources hs
|
||
WHERE hs.house_id = h.id AND hs.ext_source = 'avito')) AS houses_with_avito,
|
||
(SELECT count(*) FROM houses h
|
||
WHERE EXISTS (SELECT 1 FROM house_sources hs
|
||
WHERE hs.house_id = h.id AND hs.ext_source LIKE 'cian%')) AS houses_with_cian,
|
||
(SELECT count(*) FROM houses h
|
||
WHERE EXISTS (SELECT 1 FROM house_sources hs
|
||
WHERE hs.house_id = h.id AND hs.ext_source = 'yandex')) AS houses_with_yandex,
|
||
(SELECT count(*) FROM (
|
||
SELECT house_id FROM house_sources GROUP BY house_id HAVING count(*) >= 2
|
||
) sub) AS houses_2plus_sources,
|
||
(SELECT count(*) FROM (
|
||
SELECT house_id FROM house_sources GROUP BY house_id HAVING count(*) >= 3
|
||
) sub) AS houses_3plus_sources,
|
||
(SELECT count(*) FROM active_listings) AS listings_active,
|
||
(SELECT count(*) FROM (
|
||
SELECT listing_id FROM listing_sources
|
||
WHERE listing_id IN (SELECT id FROM active_listings)
|
||
GROUP BY listing_id HAVING count(*) >= 2
|
||
) sub) AS listings_dedup_2sources,
|
||
(SELECT count(*) FROM active_listings WHERE lat IS NOT NULL) * 100.0
|
||
/ NULLIF((SELECT count(*) FROM active_listings), 0) AS pct_geocoded,
|
||
(SELECT count(*) FROM active_listings WHERE cadastral_number IS NOT NULL) * 100.0
|
||
/ NULLIF((SELECT count(*) FROM active_listings), 0) AS pct_cadastr,
|
||
(SELECT count(*) FROM active_listings WHERE description IS NOT NULL) * 100.0
|
||
/ NULLIF((SELECT count(*) FROM active_listings), 0) AS pct_description,
|
||
(SELECT count(*) FROM active_listings l
|
||
JOIN houses h ON h.id = l.house_id_fk
|
||
WHERE h.year_built IS NOT NULL) * 100.0
|
||
/ NULLIF((SELECT count(*) FROM active_listings), 0) AS pct_year_built,
|
||
NOW() - (SELECT max(scraped_at) FROM listings WHERE source = 'avito') AS avito_last_scrape_ago,
|
||
NOW() - (SELECT max(scraped_at) FROM listings WHERE source = 'cian') AS cian_last_scrape_ago,
|
||
NOW() - (SELECT max(scraped_at) FROM listings WHERE source = 'yandex') AS yandex_last_scrape_ago,
|
||
(SELECT count(*) FROM v_price_divergence) AS price_disagreements_count,
|
||
(SELECT count(*) FROM listings WHERE is_outlier = true) AS outliers_flagged;
|
||
|
||
COMMENT ON VIEW v_data_quality IS
|
||
'KPI snapshot. Refreshed on-demand by /api/v1/admin/data-quality endpoint (Master Plan sec 8.1).';
|
||
|
||
-- audit_log: 0 rows, 0 python refs, no triggers depend on it (verified)
|
||
DROP TABLE IF EXISTS audit_log;
|
||
|
||
COMMIT;
|