Mengosongkan rekod klon daripada jadual tanpa PK

Terdapat situasi apabila ke meja tanpa kunci utama atau beberapa indeks unik lain, disebabkan oleh pengawasan, klon lengkap rekod sedia ada disertakan.

Sebagai contoh, nilai metrik kronologi ditulis ke dalam PostgreSQL menggunakan aliran COPY, dan kemudian berlaku kegagalan secara tiba-tiba, dan sebahagian daripada data yang sama sepenuhnya tiba semula.

Bagaimana untuk menghapuskan pangkalan data klon yang tidak diperlukan?

Apabila PK bukan pembantu

Cara paling mudah adalah untuk mengelakkan situasi sedemikian daripada berlaku di tempat pertama. Sebagai contoh, gulung KUNCI UTAMA. Tetapi ini tidak selalu mungkin tanpa meningkatkan jumlah data yang disimpan.

Contohnya, jika ketepatan sistem sumber lebih tinggi daripada ketepatan medan dalam pangkalan data:

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}

perasan tak? Kira detik dan bukannya 00:00:02 direkodkan dalam pangkalan data dengan ts sesaat lebih awal, tetapi kekal agak sah dari sudut aplikasi (lagipun, nilai data adalah berbeza!).

Sudah tentu anda boleh melakukannya PK(metrik, ts) - tetapi kemudian kami akan mendapat konflik sisipan untuk data yang sah.

Boleh buat PK(metrik, ts, data) - tetapi ini akan meningkatkan jumlahnya, yang tidak akan kami gunakan.

Oleh itu, pilihan yang paling betul ialah membuat indeks biasa bukan unik (metrik, ts) dan menangani masalah selepas fakta jika ia timbul.

"Perang klonik telah bermula"

Beberapa jenis kemalangan berlaku, dan sekarang kita perlu memusnahkan rekod klon dari jadual.

Mari kita modelkan data asal:

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

Di sini tangan kami menggeletar tiga kali, Ctrl+V tersangkut, dan kini...

Mula-mula, mari kita fahami bahawa jadual kita boleh menjadi sangat besar, jadi selepas kita menemui semua klon, adalah dinasihatkan untuk kita benar-benar "mencucuk jari kita" untuk memadam rekod tertentu tanpa menyelidikinya semula.

Dan ada cara sedemikian - ini menangani oleh ctid, pengecam fizikal rekod tertentu.

Iaitu, pertama sekali, kita perlu mengumpul ctid rekod dalam konteks kandungan lengkap baris jadual. Pilihan paling mudah ialah menghantar keseluruhan baris ke dalam teks:

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

Adakah mungkin untuk tidak membuang?Pada dasarnya, ia mungkin dalam kebanyakan kes. Sehingga anda mula menggunakan medan dalam jadual ini jenis tanpa pengendali kesamarataan:

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

Ya, kami segera melihat bahawa jika terdapat lebih daripada satu entri dalam tatasusunan, ini semua adalah klon. Mari tinggalkan mereka:

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)

Bagi mereka yang suka menulis pendekAnda juga boleh menulisnya seperti ini:

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

Oleh kerana nilai rentetan bersiri itu sendiri tidak menarik bagi kami, kami hanya membuangnya keluar dari lajur yang dikembalikan subquery.

Ada sedikit lagi yang perlu dilakukan - jadikan DELETE menggunakan set yang kami terima:

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

Mari semak diri kita:

[lihat explain.tensor.ru]

Ya, semuanya betul: 3 rekod kami telah dipilih untuk satu-satunya Imbasan Seq bagi keseluruhan jadual, dan nod Padam digunakan untuk mencari data pas tunggal dengan Tid Scan:

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

Jika anda mengosongkan banyak rekod, jangan lupa jalankan VACUUM ANALYZE.

Mari kita semak untuk jadual yang lebih besar dan dengan bilangan pendua yang lebih besar:

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;

[lihat explain.tensor.ru]

Jadi, kaedah ini berjaya, tetapi ia mesti digunakan dengan berhati-hati. Kerana untuk setiap rekod yang dipadamkan, terdapat satu halaman data dibaca dalam Tid Scan dan satu dalam Delete.

Sumber: www.habr.com