Изчистване на клонирани записи от таблица без PK

Има ситуации, когато към таблица без първичен ключ или някакъв друг уникален индекс, поради пропуск са включени пълни клонинги на вече съществуващи записи.

Например, стойностите на хронологичен показател се записват в PostgreSQL с помощта на COPY поток и след това има внезапна повреда и част от напълно идентичните данни пристигат отново.

Как да изчистя базата данни от ненужни клонинги?

Когато ПК не е помощник

Най-лесният начин е да предотвратите възникването на такава ситуация на първо място. Например хвърлете ПЪРВИЧЕН КЛЮЧ. Но това не винаги е възможно без увеличаване на обема на съхраняваните данни.

Например, ако точността на изходната система е по-висока от точността на полето в базата данни:

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 секунда по-рано, но остана доста валидно от гледна точка на приложението (в края на краищата стойностите на данните са различни!).

Разбира се, че можете да го направите PK(метрика, ts) - но тогава ще получим конфликти при вмъкване за валидни данни.

Може да направи PK(метрика, ts, данни) - но това ще увеличи много обема му, което няма да използваме.

Следователно най-правилният вариант е да направите обикновен неуникален индекс (метрика, 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 на записите в контекста на пълното съдържание на реда на таблицата. Най-простият вариант е да прехвърлите целия ред в текст:

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

Нека проверим себе си:

[вижте expand.tensor.ru]

Да, всичко е правилно: нашите 3 записа бяха избрани за единственото Seq сканиране на цялата таблица и възелът Изтриване беше използван за търсене на данни едно минаване с Tid Scan:

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

Ако сте изчистили много записи, не забравяйте да стартирате VACUUM ANALYSE.

Нека проверим за по-голяма таблица и с по-голям брой дубликати:

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;

[вижте expand.tensor.ru]

И така, методът работи успешно, но трябва да се използва с известно внимание. Тъй като за всеки запис, който е изтрит, има една страница с данни, прочетена в Tid Scan, и една в Delete.

Източник: www.habr.com