33 lines
1.3 KiB
MySQL
33 lines
1.3 KiB
MySQL
|
-- migrate:up
|
||
|
|
--
|
||
|
|
-- One-time cleanup + guard for state.position_history duplicates.
|
||
|
|
--
|
||
|
|
-- A parked device re-reports the same gpsTime on every 60s poll, so before the
|
||
|
|
-- projector's write-time NOT EXISTS guard (shipped alongside this migration)
|
||
|
|
-- the history hypertable accumulated identical (imei, occurred_at) rows —
|
||
|
|
-- bloating storage and inflating the "fix count" shown in the trip dock.
|
||
|
|
--
|
||
|
|
-- Step 1 delete duplicates, keeping the lowest history_id per
|
||
|
|
-- (imei, occurred_at).
|
||
|
|
-- Step 2 add a unique index so duplicates can never reappear. It includes
|
||
|
|
-- occurred_at (the hypertable partition column), which TimescaleDB
|
||
|
|
-- requires for a unique index on a hypertable.
|
||
|
|
--
|
||
|
|
-- NOTE: the DELETE scans the whole hypertable; run it in a quiet window. The
|
||
|
|
-- migration is independent of the projector deploy order — the code's
|
||
|
|
-- NOT EXISTS guard needs no constraint, so applying this before/after a
|
||
|
|
-- redeploy is both safe.
|
||
|
|
|
||
|
|
DELETE FROM state.position_history a
|
||
|
|
USING state.position_history b
|
||
|
|
WHERE a.imei = b.imei
|
||
|
|
AND a.occurred_at = b.occurred_at
|
||
|
|
AND a.history_id > b.history_id;
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS position_history_imei_occurred_uq
|
||
|
|
ON state.position_history (imei, occurred_at);
|
||
|
|
|
||
|
|
-- migrate:down
|
||
|
|
|
||
|
|
DROP INDEX IF EXISTS state.position_history_imei_occurred_uq;
|