Чистење на записи за клонови од табела без 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[]);

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

[погледнете на објаснување.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))

Ако расчистивте многу записи, Не заборавајте да извршите вакуумски анализи.

Ајде да провериме за поголема табела и со поголем број дупликати:

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;

[погледнете на објаснување.tensor.ru]

Значи, методот работи успешно, но мора да се користи со одредена претпазливост. Бидејќи за секој запис што е избришан, има една страница со податоци прочитана во Tid Scan и една во Delete.

Извор: www.habr.com