PK olmayan cədvəldən klon qeydlərinin təmizlənməsi

Elə hallar olur ki əsas açarı olmayan masaya və ya başqa bir unikal indeks, bir nəzarət səbəbiylə, artıq mövcud qeydlərin tam klonları daxil edilir.

Məsələn, xronoloji metrikanın dəyərləri KOPYA axınından istifadə edərək PostgreSQL-ə yazılır və sonra qəfil uğursuzluq baş verir və tamamilə eyni məlumatların bir hissəsi yenidən gəlir.

Verilənlər bazasını lazımsız klonlardan necə təmizləmək olar?

PK köməkçi olmadığı zaman

Ən asan yol, ilk növbədə belə bir vəziyyətin baş verməsinin qarşısını almaqdır. Məsələn, PRIMARY KEY-i yuvarlayın. Lakin bu, saxlanılan məlumatların həcmini artırmadan həmişə mümkün olmur.

Məsələn, mənbə sisteminin dəqiqliyi verilənlər bazasındakı sahənin dəqiqliyindən yüksəkdirsə:

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}

Siz fərq etdiniz? 00:00:02 əvəzinə geri sayım verilənlər bazasında bir saniyə əvvəl ts ilə qeyd edildi, lakin tətbiq baxımından olduqca etibarlı qaldı (hər şeydən sonra məlumat dəyərləri fərqlidir!).

Əlbəttə ki, edə bilərsiniz PK(metrik, ts) - lakin sonra etibarlı məlumatlar üçün daxiletmə ziddiyyətləri alacağıq.

Edə bilərsiniz PK(metrik, ts, data) - lakin bu, istifadə etməyəcəyimiz həcmini çox artıracaq.

Buna görə də, ən düzgün seçim müntəzəm qeyri-unikal indeks etməkdir (metrik, ts) və problem yarandıqda onlarla məşğul olun.

"Klonik müharibə başladı"

Bir növ qəza baş verdi və indi cədvəldəki klon qeydlərini məhv etməliyik.

Orijinal məlumatları modelləşdirək:

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

Burada əlimiz üç dəfə titrədi, Ctrl+V ilişdi, indi...

Əvvəlcə başa düşək ki, masamız çox böyük ola bilər, buna görə də bütün klonları tapdıqdan sonra silmək üçün sözün əsl mənasında "barmağını soxmağımız" məsləhətdir. xüsusi qeydləri təkrar axtarmadan.

Və belə bir yol var - bu ctid tərəfindən ünvanlanır, xüsusi qeydin fiziki identifikatoru.

Yəni, ilk növbədə, cədvəl sırasının tam məzmunu kontekstində qeydlərin ctidini toplamaq lazımdır. Ən sadə seçim bütün sətri mətnə ​​çevirməkdir:

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

Verməmək olar?Prinsipcə, əksər hallarda mümkündür. Bu cədvəldəki sahələrdən istifadə etməyə başlayana qədər bərabərlik operatoru olmayan növlər:

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

Bəli, biz dərhal görürük ki, serialda birdən çox giriş varsa, bunların hamısı klonlardır. Gəlin onları tərk edək:

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)

Qısa yazmağı sevənlər üçünSiz də belə yaza bilərsiniz:

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

Seriallaşdırılmış sətirin dəyəri bizim üçün maraqlı olmadığı üçün onu sadəcə alt sorğunun qaytarılmış sütunlarından atdıq.

Bir az qalıb - DELETE-i aldığımız dəstdən istifadə edin:

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

Gəlin özümüzü yoxlayaq:

[express.tensor.ru saytına baxın]

Bəli, hər şey düzgündür: 3 qeydimiz bütün cədvəlin yeganə ardıcıl skanı üçün seçilmişdir və məlumatların axtarışı üçün Sil qovşağından istifadə edilmişdir. Tid Scan ilə tək keçid:

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

Bir çox qeydi silmisinizsə, VAKUUM ANALİZİ işə salmağı unutmayın.

Gəlin daha böyük bir cədvəl və daha çox sayda dublikatları yoxlayaq:

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;

[express.tensor.ru saytına baxın]

Beləliklə, üsul uğurla işləyir, lakin bir qədər ehtiyatla istifadə edilməlidir. Çünki silinən hər qeyd üçün Tid Scan-da oxunan bir məlumat səhifəsi və Sildə bir məlumat səhifəsi var.

Mənbə: www.habr.com