fix(22j): DEDUP domrf_kn_infrastructure + ON CONFLICT (issue #297) #304

Merged

lekss361 merged 1 commit from fix/22j-dedup-v3 into main

2026-05-17 15:15:04 +00:00

lekss361 commented

2026-05-17 15:12:37 +00:00

Owner

Audit numbers (postgres MCP)

Metric	Value
total_rows	496 153
unique by `(obj_id, poi_category, poi_name, poi_address)`	229 261
duplicates	~267 000 (~54%)
obj_id=65136 sample	374 rows vs 192 на сайте = ×2 confirmed

Root cause

Старый ON CONFLICT (obj_id, poi_name, poi_lat, poi_lon, snapshot_date) DO UPDATE — snapshot_date в ключе → каждый scrape run вставлял новые строки вместо UPDATE → накопление ×N (N = количество scrape runs).

Changes

`data/sql/110_22j_dedup_infrastructure.sql`

ONE-TIME DELETE keeping min(ctid) per (obj_id, poi_category, poi_name, poi_address)
DROP CONSTRAINT domrf_kn_infrastructure_obj_id_poi_name_poi_lat_poi_lon_sna_key (old wrong granularity, idempotent skip if absent)
ADD CONSTRAINT uq_infra_dedupe UNIQUE NULLS NOT DISTINCT (obj_id, poi_category, poi_name, poi_address) (PG16, handles nullable poi_category/poi_address)

`backend/app/services/scrapers/domrf_kn.py`

UPSERT_INFRA_SQL — ON CONFLICT key matches new constraint, DO NOTHING (POI fields immutable, нет смысла update'ить):

ON CONFLICT (obj_id, poi_category, poi_name, poi_address) DO NOTHING

Deploy order

Apply data/sql/110_22j_dedup_infrastructure.sql first (auto через _schema_migrations).
Deploy backend.

Verify

SELECT COUNT(*), COUNT(DISTINCT (obj_id, poi_category, poi_name, poi_address))
FROM domrf_kn_infrastructure;
-- Должны совпадать. obj_id=65136 — ~192 rows (не 374).

Related: issue #297 sub-task 22j

## Audit numbers (postgres MCP) | Metric | Value | |---|---| | total_rows | **496 153** | | unique by `(obj_id, poi_category, poi_name, poi_address)` | 229 261 | | **duplicates** | **~267 000 (~54%)** | | obj_id=65136 sample | 374 rows vs 192 на сайте = ×2 confirmed | ## Root cause Старый `ON CONFLICT (obj_id, poi_name, poi_lat, poi_lon, snapshot_date) DO UPDATE` — `snapshot_date` в ключе → каждый scrape run вставлял новые строки вместо UPDATE → накопление ×N (N = количество scrape runs). ## Changes ### `data/sql/110_22j_dedup_infrastructure.sql` 1. ONE-TIME `DELETE` keeping `min(ctid)` per `(obj_id, poi_category, poi_name, poi_address)` 2. `DROP CONSTRAINT domrf_kn_infrastructure_obj_id_poi_name_poi_lat_poi_lon_sna_key` (old wrong granularity, idempotent skip if absent) 3. `ADD CONSTRAINT uq_infra_dedupe UNIQUE NULLS NOT DISTINCT (obj_id, poi_category, poi_name, poi_address)` (PG16, handles nullable poi_category/poi_address) ### `backend/app/services/scrapers/domrf_kn.py` `UPSERT_INFRA_SQL` — ON CONFLICT key matches new constraint, `DO NOTHING` (POI fields immutable, нет смысла update'ить): ```sql ON CONFLICT (obj_id, poi_category, poi_name, poi_address) DO NOTHING ``` ## Deploy order 1. Apply `data/sql/110_22j_dedup_infrastructure.sql` first (auto через `_schema_migrations`). 2. Deploy backend. ## Verify ```sql SELECT COUNT(*), COUNT(DISTINCT (obj_id, poi_category, poi_name, poi_address)) FROM domrf_kn_infrastructure; -- Должны совпадать. obj_id=65136 — ~192 rows (не 374). ``` Related: issue #297 sub-task 22j

lekss361 added 1 commit 2026-05-17 15:12:38 +00:00

fix(22j): DEDUP domrf_kn_infrastructure + ON CONFLICT (issue #297 ) 7863a99394

Audit (2026-05-17): 496 153 total rows, 229 261 unique by
(obj_id, poi_category, poi_name, poi_address) = ~267k duplicates (~54%).
obj_id=65136 sample: 374 rows vs 192 on site = ×2 confirmed.

Root cause: old ON CONFLICT key included snapshot_date — each scrape run
inserted fresh rows for same POI. Accumulated ×N copies = N scrape runs.

Changes:
- data/sql/110_22j_dedup_infrastructure.sql: ONE-TIME cleanup keeping min(ctid)
  + DROP old constraint + ADD UNIQUE NULLS NOT DISTINCT (PG16) for nullable
  poi_category/poi_address.
- backend/app/services/scrapers/domrf_kn.py UPSERT_INFRA_SQL: ON CONFLICT
  (obj_id, poi_category, poi_name, poi_address) DO NOTHING — without snapshot_date.

Closes issue #297 sub-task 22j.

lekss361 commented

2026-05-17 15:14:55 +00:00

Author

Owner

Deep Code Review — verdict: APPROVE

Pre-merge checks

PR state=open, mergeable=true, base=main, head=7863a99
Old constraint name verified через postgres MCP: domrf_kn_infrastructure_obj_id_poi_name_poi_lat_poi_lon_sna_key — exact match с DROP в миграции
No FK / VIEW dependencies на эту таблицу (только 2 индекса: idx_infra_obj, idx_infra_cat_dist — оба переживут)
Old ON CONFLICT key точно соответствует тому что был в БД: (obj_id, poi_name, poi_lat, poi_lon, snapshot_date)
Sequential numbering OK: 108 → (109/110 reserved) → 110_22j (THIS) → 111_22f → 112_22i
Vault entry fixes/Fix_BUG22j_Infrastructure_Dedup_May17.md уже создана
BEGIN/COMMIT обёртка ✓, idempotent (DELETE self-no-op, DROP IF EXISTS, ADD на чистых данных) ✓
backend UPSERT_INFRA_SQL columns точно совпадают с новым UNIQUE — (obj_id, poi_category, poi_name, poi_address) ✓

Cross-file impact

Backend ON CONFLICT key === SQL UNIQUE constraint columns — совпадают 1:1
POI fields immutable → DO NOTHING корректно (subtitle/distance_m не критичны для пересмотра)
Никто из downstream не зависит от старого constraint name (grep по proj.)

EXPLAIN на DELETE (cost estimate)

Plan: Merge Join + 2× Sort + 2× Seq Scan по 495 628 rows; cost ≈ 244 113..257 743
На production VM ожидаемо несколько минут lock'а на таблицу — auto-apply через _schema_migrations это переживёт, downstream scrapers просто подождут
После dedup constraint check на 229k rows — fast

Minor nits (non-blocking)

Header в файле говорит 100_22j_dedup_infrastructure.sql (унаследовано из vault draft), фактическое имя файла 110_22j_* — расхождение в комментарии, не влияет на execution
DO NOTHING (вместо DO UPDATE для distance_m) — если POI «переедет» (новый адрес или категория переименуется на DOM.РФ) — будет вставлена новая строка как другой логический POI, старая останется. Это acceptable per task scope, но flag для будущего monitoring

Destructive note

Миграция удалит ~267 000 строк (54%) — irreversible после COMMIT
Auto-apply через GHA → миграция запустится сразу после merge

Verify после deploy:

SELECT COUNT(*), COUNT(DISTINCT (obj_id, poi_category, poi_name, poi_address))
FROM domrf_kn_infrastructure;
-- должны совпадать; obj_id=65136 → ~192 rows

Approved — merging via squash.

## Deep Code Review — verdict: APPROVE ### Pre-merge checks - PR `state=open`, `mergeable=true`, base=main, head=`7863a99` - Old constraint name verified через postgres MCP: `domrf_kn_infrastructure_obj_id_poi_name_poi_lat_poi_lon_sna_key` — exact match с DROP в миграции - No FK / VIEW dependencies на эту таблицу (только 2 индекса: `idx_infra_obj`, `idx_infra_cat_dist` — оба переживут) - Old ON CONFLICT key точно соответствует тому что был в БД: `(obj_id, poi_name, poi_lat, poi_lon, snapshot_date)` - Sequential numbering OK: `108 → (109/110 reserved) → 110_22j (THIS) → 111_22f → 112_22i` - Vault entry `fixes/Fix_BUG22j_Infrastructure_Dedup_May17.md` уже создана - BEGIN/COMMIT обёртка ✓, idempotent (DELETE self-no-op, DROP IF EXISTS, ADD на чистых данных) ✓ - backend `UPSERT_INFRA_SQL` columns точно совпадают с новым UNIQUE — `(obj_id, poi_category, poi_name, poi_address)` ✓ ### Cross-file impact - Backend ON CONFLICT key === SQL UNIQUE constraint columns — совпадают 1:1 - POI fields immutable → `DO NOTHING` корректно (subtitle/distance_m не критичны для пересмотра) - Никто из downstream не зависит от старого constraint name (grep по proj.) ### EXPLAIN на DELETE (cost estimate) - Plan: Merge Join + 2× Sort + 2× Seq Scan по 495 628 rows; cost ≈ **244 113..257 743** - На production VM ожидаемо несколько минут lock'а на таблицу — auto-apply через `_schema_migrations` это переживёт, downstream scrapers просто подождут - После dedup constraint check на 229k rows — fast ### Minor nits (non-blocking) - Header в файле говорит `100_22j_dedup_infrastructure.sql` (унаследовано из vault draft), фактическое имя файла `110_22j_*` — расхождение в комментарии, не влияет на execution - `DO NOTHING` (вместо `DO UPDATE` для distance_m) — если POI «переедет» (новый адрес или категория переименуется на DOM.РФ) — будет вставлена новая строка как другой логический POI, старая останется. Это acceptable per task scope, но flag для будущего monitoring ### Destructive note - Миграция удалит **~267 000 строк** (54%) — irreversible после COMMIT - Auto-apply через GHA → миграция запустится сразу после merge - Verify после deploy: ```sql SELECT COUNT(*), COUNT(DISTINCT (obj_id, poi_category, poi_name, poi_address)) FROM domrf_kn_infrastructure; -- должны совпадать; obj_id=65136 → ~192 rows ``` Approved — merging via squash.