Вычышчаем клон-запісы з табліцы без PK

Здараюцца сітуацыі, калі у табліцу без першаснага ключа ці нейкага іншага ўнікальнага азначніка па недаглядзе трапляюць поўныя клоны ўжо існых запісаў.

Напрыклад, пішуцца ў PostgreSQL COPY-струменем значэння храналагічнай метрыкі, а потым раптоўны збой, і частка цалкам ідэнтычных дадзеных прыходзіць паўторна.

Як пазбавіць базу ад непатрэбных клонаў?

Калі PK не памочнік

Самы просты спосаб - наогул не дапусціць узнікнення такой сітуацыі. Напрыклад, накаціць-ткі PRIMARY KEY. Але гэта магчыма не заўсёды без павелічэння аб'ёму захаваных даных.

Напрыклад, калі дакладнасць зыходнай сістэмы вышэй, чым дакладнасць поля ў БД:

metric   | ts                  | data
--------------------------------------------------
cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 10}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2}
cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7}

Заўважылі? Адлік замест 00:00:02 запісаўся ў базу з ts на секунду раней, але застаўся суцэль валідным з прыкладнага пункта гледжання (бо значэнні data – розныя!).

Вядома, можна зрабіць PK(metric, ts) - Але тады мы будзем атрымліваць канфлікты ўстаўкі для валідных дадзеных.

можна зрабіць PK(metric, ts, data) - але гэта моцна павялічыць яго аб'ём, якім мы і карыстацца-то не будзем.

Таму самы правільны варыянт - зрабіць звычайны неўнікальны індэкс (metric, ts) і разбірацца з праблемамі постфактум, калі яны ўсё ж такі ўзнікнуць.

«Вайна кланічная пачалася»

Здарылася нейкая аварыя, і зараз нам трэба будзе знішчыць клон-запісы з табліцы.

Давайце змадэлюем зыходныя дадзеныя:

CREATE TABLE tbl(k text, v integer);

INSERT INTO tbl
VALUES
  ('a', 1)
, ('a', 3)
, ('b', 2)
, ('b', 2) -- oops!
, ('c', 3)
, ('c', 3) -- oops!!
, ('c', 3) -- oops!!
, ('d', 4)
, ('e', 5)
;

Тут у нас тройчы завагалася рука, заліп Ctrl+V, і вось…

Спачатку давайце зразумеем, што табліца ў нас можа быць вельмі немаленькай, таму пасля таго, як мы знойдзем усе клоны, нам пажадана літаральна "тыкаць пальцам", каб выдаляць канкрэтныя запісы без паўторнага іх пошуку.

І такі спосаб ёсць - гэта адрасаванне па ctid, фізічнаму ідэнтыфікатару канкрэтнага запісу.

Гэта значыць, перш за ўсё, нам трэба сабраць ctid запісаў у разрэзе поўнага кантэнту радка табліцы. Самы проста варыянт - скаставаць увесь радок у text:

SELECT
  T::text
, array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  1;

t     | ctids
---------------------------------
(e,5) | {"(0,9)"}
(d,4) | {"(0,8)"}
(c,3) | {"(0,5)","(0,6)","(0,7)"}
(b,2) | {"(0,3)","(0,4)"}
(a,3) | {"(0,2)"}
(a,1) | {"(0,1)"}

А ці можна не каставаць?У прынцыпе - можна ў большасці выпадкаў. Пакуль вы не пачняце выкарыстоўваць у гэтай табліцы поля тыпаў без аператара роўнасці:

CREATE TABLE tbl(k text, v integer, x point);
SELECT
  array_agg(ctid) ctids
FROM
  tbl T
GROUP BY
  T;
-- ERROR:  could not identify an equality operator for type tbl

Ага, адразу бачым, што калі ў масіве аказалася больш за адзін запіс — гэта ўсё і ёсць клоны. Давайце пакінем толькі іх:

SELECT
  unnest(ctids[2:])
FROM
  (
    SELECT
      array_agg(ctid) ctids
    FROM
      tbl T
    GROUP BY
      T::text
  ) T;

unnest
------
(0,6)
(0,7)
(0,4)

Аматарам пісаць карацейМожна напісаць і вось так:

SELECT
  unnest((array_agg(ctid))[2:])
FROM
  tbl T
GROUP BY
  T::text;

Паколькі само значэнне серыялізаванага радка нам нецікава, то мы яго проста выкінулі з слупкоў подзапроса.

Засталося ўсяго няшмат – прымусіць DELETE выкарыстоўваць атрыманы намі набор:

DELETE FROM
  tbl
WHERE
  ctid = ANY(ARRAY(
    SELECT
      unnest(ctids[2:])
    FROM
      (
        SELECT
          array_agg(ctid) ctids
        FROM
          tbl T
        GROUP BY
          T::text
      ) T
  )::tid[]);

Праверым сябе:

[глядзець на explain.tensor.ru]

Так, усё правільна: нашы 3 запісы адабраліся за адзіны Seq Scan усёй табліцы, а Delete-вузел выкарыстоўваў для пошуку дадзеных аднаразовы праход з дапамогай Tid Scan:

->  Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
      TID Cond: (ctid = ANY ($0))

Калі зачысцілі шмат запісаў, не забываем прагнаць VACUUM ANALYZE.

Праверым для табліцы пабольш і з вялікай колькасцю дубляў:

TRUNCATE TABLE tbl;

INSERT INTO tbl
SELECT
  chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z
, (random() * 100)::integer v -- 0..99
FROM
  generate_series(1, 10000) i;

[глядзець на explain.tensor.ru]

Такім чынам, спосаб паспяхова працуе, але прымяняць трэба з вядомай асцярожнасцю. Таму што на кожны выдаляны запіс прыходзіцца адно чытанне старонкі дадзеных у Tid Scan, і адно - у Delete.

Крыніца: habr.com