PK'sız bir tablodan klon kayıtlarını temizleme

Öyle durumlar var ki birincil anahtarı olmayan bir tabloya veya başka bir benzersiz indeks, bir gözetim nedeniyle, halihazırda mevcut kayıtların tam kopyalarını içerir.

Örneğin, kronolojik bir metriğin değerleri bir COPY akışı kullanılarak PostgreSQL'e yazılır ve ardından ani bir arıza meydana gelir ve tamamen aynı verilerin bir kısmı tekrar gelir.

Veritabanını gereksiz klonlardan nasıl kurtarabilirim?

PK yardımcı olmadığında

En kolay yol, ilk etapta böyle bir durumun oluşmasını engellemektir. Örneğin, PRIMARY KEY'i yuvarlayın. Ancak depolanan verilerin hacmini artırmadan bu her zaman mümkün değildir.

Örneğin kaynak sistemin doğruluğu veri tabanındaki alanın doğruluğundan yüksekse:

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}

Fark ettin mi? 00:00:02 yerine geri sayım veritabanına bir saniye önce ts ile kaydedildi, ancak uygulama açısından oldukça geçerli kaldı (sonuçta veri değerleri farklı!).

Tabii ki yapabilirsin PK(metrik, ts) - ancak daha sonra geçerli veriler için ekleme çakışmaları alacağız.

Yapabilir PK(metrik, ts, veri) - ancak bu, kullanmayacağımız hacmini büyük ölçüde artıracaktır.

Bu nedenle en doğru seçenek, düzenli, benzersiz olmayan bir dizin oluşturmaktır. (metrik, ts) ve eğer ortaya çıkarsa sorunlarla olaydan sonra ilgilenin.

"Klonik savaş başladı"

Bir tür kaza oldu ve şimdi klon kayıtlarını tablodan yok etmemiz gerekiyor.

Orijinal verileri modelleyelim:

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 elimiz üç kez titredi, Ctrl+V sıkıştı ve şimdi...

Öncelikle masamızın çok büyük olabileceğini anlayalım, bu nedenle tüm klonları bulduktan sonra silmek için kelimenin tam anlamıyla "parmağımızı sokmamız" tavsiye edilir. belirli kayıtları yeniden aramadan.

Ve böyle bir yol var - bu ctid ile adresleme, belirli bir kaydın fiziksel tanımlayıcısı.

Yani öncelikle tablo satırının tam içeriği bağlamında kayıtların ctid'sini toplamamız gerekiyor. En basit seçenek, satırın tamamını metne dönüştürmektir:

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

Atılmamak mümkün mü?Prensip olarak çoğu durumda mümkündür. Bu tablodaki alanları kullanmaya başlayıncaya kadar eşitlik operatörü olmayan türler:

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

Evet, dizide birden fazla giriş varsa bunların hepsinin klon olduğunu hemen görüyoruz. Bunları bırakalım:

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)

Kısa yazmayı sevenler içinBunu şu şekilde de yazabilirsiniz:

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

Serileştirilmiş dizenin değeri bizim için ilginç olmadığından, onu alt sorgunun döndürülen sütunlarından çıkardık.

Yapılacak çok az şey kaldı - DELETE'in aldığımız seti kullanmasını sağlayın:

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

Kendimizi kontrol edelim:

[açıklama.tensor.ru'ya bakın]

Evet, her şey doğru: 3 kaydımız tüm tablonun tek Sıralı Taraması için seçildi ve veri aramak için Sil düğümü kullanıldı Tid Scan ile tek geçiş:

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

Çok sayıda kaydı temizlediyseniz, VAKUM ANALİZİNİ çalıştırmayı unutmayın.

Daha büyük bir tabloyu ve daha fazla sayıda kopyayı kontrol edelim:

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;

[açıklama.tensor.ru'ya bakın]

Yani yöntem başarılı bir şekilde çalışıyor ancak dikkatli kullanılması gerekiyor. Çünkü silinen her kayıt için Tid Scan'de okunan bir veri sayfası, Sil'de ise bir veri sayfası okunur.

Kaynak: habr.com