Брисање клонираних записа из табеле без ПК

Постоје ситуације када на табелу без примарног кључа или неки други јединствени индекс, због превида, укључени су комплетни клонови већ постојећих записа.

На пример, вредности хронолошке метрике се уписују у ПостгреСКЛ помоћу ЦОПИ тока, а онда долази до изненадног квара и поново стиже део потпуно идентичних података.

Како ослободити базу података непотребних клонова?

Када ПК није помоћник

Најлакши начин је спречити да дође до такве ситуације. На пример, баците ПРИМАРНИ КЉУЧ. Али то није увек могуће без повећања обима ускладиштених података.

На пример, ако је тачност изворног система већа од тачности поља у бази података:

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 је забележено у бази података са тс секундом раније, али је остало прилично валидно са становишта апликације (на крају крајева, вредности података су различите!).

Наравно да можете ПК(метрика, тс) - али тада ћемо добити конфликте уметања за важеће податке.

Могу ПК(метрика, тс, подаци) - али ово ће у великој мери повећати њен обим, који нећемо користити.

Стога је најисправнија опција да се направи обичан нејединствен индекс (метрика, тс) и решавати проблеме после чињенице ако се појаве.

"Клонички рат је почео"

Десила се нека несрећа, и сада морамо да уништимо записе клонова са стола.

Хајде да моделујемо оригиналне податке:

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)
;

Овде нам је рука задрхтала три пута, Цтрл+В се заглавио, а сад...

Прво, хајде да схватимо да наша табела може бити веома велика, па након што пронађемо све клонове, препоручљиво је да буквално „бодемо прстом“ за брисање одређене записе без њиховог поновног претраживања.

А постоји и такав начин - овај ословљавање по цтид, физички идентификатор одређеног записа.

То јест, пре свега, треба да прикупимо цтид записа у контексту комплетног садржаја реда табеле. Најједноставнија опција је да се цео ред претвори у текст:

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 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[]);

Хајде да се проверимо:

[погледајте на објасни.тенсор.ру]

Да, све је тачно: наша 3 записа су изабрана за једино Сек скенирање целе табеле, а чвор Делете је коришћен за претрагу података један пролаз са Тид Сцан:

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

Ако сте обрисали много записа, не заборавите да покренете ВАЦУУМ АНАЛИЗЕ.

Хајде да проверимо да ли постоји већа табела и са већим бројем дупликата:

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;

[погледајте на објасни.тенсор.ру]

Дакле, метода функционише успешно, али се мора користити са одређеним опрезом. Зато што за сваки запис који се брише, постоји једна страница са подацима која се чита у Тид Сцан, а једна у Делете.

Извор: ввв.хабр.цом