Czyszczenie rekordów klonowania z tabeli bez PK

Są sytuacje, kiedy do tabeli bez klucza podstawowego lub inny unikalny indeks, w wyniku przeoczenia uwzględniane są kompletne klony już istniejących rekordów.

Na przykład wartości metryki chronologicznej są zapisywane do PostgreSQL za pomocą strumienia COPY, a następnie następuje nagła awaria i część całkowicie identycznych danych pojawia się ponownie.

Jak pozbyć się bazy danych ze zbędnych klonów?

Kiedy PK nie jest pomocnikiem

Najłatwiej jest w pierwszej kolejności zapobiec takim sytuacjom. Na przykład rzuć KLUCZ PODSTAWOWY. Jednak nie zawsze jest to możliwe bez zwiększenia ilości przechowywanych danych.

Przykładowo, jeśli dokładność systemu źródłowego jest większa niż dokładność pola w bazie danych:

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}

Czy zauważyłeś? Odliczanie zamiast 00:00:02 zostało zapisane w bazie danych z ts sekundę wcześniej, ale z aplikacyjnego punktu widzenia pozostało całkiem aktualne (w końcu wartości danych są inne!).

Oczywiście, że możesz to zrobić PK(metryczne, ts) - ale wtedy wystąpią konflikty wstawiania dla prawidłowych danych.

Można zrobić PK(metryka, ts, dane) - ale to znacznie zwiększy jego głośność, z której nie skorzystamy.

Dlatego najbardziej poprawną opcją jest utworzenie zwykłego, nieunikalnego indeksu (metryczne, ts) i rozwiązuj problemy po fakcie, jeśli się pojawią.

„Rozpoczęła się wojna kloniczna”

Wydarzył się jakiś wypadek i teraz musimy zniszczyć zapisy klonów ze stołu.

Modelujmy oryginalne dane:

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

Tutaj nasza ręka zadrżała trzy razy, Ctrl+V utknęło i teraz...

Najpierw zrozummy, że nasz stół może być bardzo duży, więc po znalezieniu wszystkich klonów wskazane jest, abyśmy dosłownie „szturchnęli palcem”, aby usunąć konkretnych zapisów bez ponownego ich przeszukiwania.

I jest taki sposób - to adresowanie przez ctid, fizyczny identyfikator konkretnego rekordu.

Oznacza to, że przede wszystkim musimy zebrać ctid rekordów w kontekście pełnej zawartości wiersza tabeli. Najprostszą opcją jest rzutowanie całej linii na tekst:

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)"}

Czy można nie rzucać?W zasadzie w większości przypadków jest to możliwe. Dopóki nie zaczniesz korzystać z pól w tej tabeli typy bez operatora równości:

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

Tak, od razu widzimy, że jeśli w tablicy jest więcej niż jeden wpis, to wszystkie są klonami. Zostawmy je:

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)

Dla tych, którzy lubią pisać krócejMożna to też zapisać w ten sposób:

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

Ponieważ sama wartość serializowanego ciągu nie jest dla nas interesująca, po prostu wyrzuciliśmy go ze zwróconych kolumn podzapytania.

Pozostało jeszcze trochę do zrobienia - niech DELETE użyje zestawu, który otrzymaliśmy:

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

Sprawdźmy sami:

[patrz na explain.tensor.ru]

Tak, wszystko się zgadza: nasze 3 rekordy zostały wybrane do jedynego skanowania sekwencyjnego całej tabeli, a do wyszukiwania danych użyto węzła Usuń jednoprzebiegowe z Tid Scan:

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

Jeśli wyczyściłeś wiele rekordów, nie zapomnij uruchomić ANALIZY PRÓŻNIOWEJ.

Sprawdźmy dla większej tabeli i z większą liczbą duplikatów:

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;

[patrz na explain.tensor.ru]

Zatem metoda działa skutecznie, ale należy ją stosować z pewną ostrożnością. Ponieważ dla każdego usuniętego rekordu jedna strona danych jest odczytywana w Tid Scan, a druga w Delete.

Źródło: www.habr.com