Menghapus catatan klon dari tabel tanpa PK

Ada situasi ketika ke tabel tanpa kunci utama atau indeks unik lainnya, karena kelalaian, klon lengkap dari catatan yang sudah ada disertakan.

Misalnya, nilai metrik kronologis ditulis ke PostgreSQL menggunakan aliran COPY, dan kemudian terjadi kegagalan mendadak, dan sebagian dari data yang sepenuhnya identik tiba lagi.

Bagaimana cara menghilangkan database klon yang tidak perlu?

Ketika PK tidak menjadi penolong

Cara termudah adalah mencegah situasi seperti ini terjadi. Misalnya, putar PRIMARY KEY. Namun hal ini tidak selalu mungkin dilakukan tanpa meningkatkan volume data yang disimpan.

Misalnya, jika keakuratan sistem sumber lebih tinggi daripada keakuratan kolom dalam database:

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}

Apakah kamu menyadari? Hitung mundur alih-alih 00:00:02 dicatat dalam database dengan ts sedetik sebelumnya, tetapi tetap cukup valid dari sudut pandang aplikasi (bagaimanapun juga, nilai datanya berbeda!).

Tentu saja Anda bisa melakukannya PK (metrik, ts) - tapi kemudian kita akan mendapatkan konflik penyisipan untuk data yang valid.

Bisa melakukan PK(metrik, ts, data) - tapi ini akan sangat meningkatkan volumenya, yang tidak akan kita gunakan.

Oleh karena itu, opsi yang paling tepat adalah membuat indeks reguler yang tidak unik (metrik, ts) dan menangani masalah setelah kejadiannya jika masalah itu muncul.

"Perang klonik telah dimulai"

Semacam kecelakaan terjadi, dan sekarang kita harus menghancurkan catatan klon dari tabel.

Mari kita memodelkan data asli:

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 gemetar tiga kali, Ctrl+V macet, dan sekarang...

Pertama, mari kita pahami bahwa tabel kita bisa sangat besar, jadi setelah kita menemukan semua klon, disarankan bagi kita untuk “menyodok jari kita” untuk menghapusnya. catatan tertentu tanpa mencarinya kembali.

Dan ada cara seperti itu - ini ditangani oleh ctid, pengidentifikasi fisik dari catatan tertentu.

Artinya, pertama-tama, kita perlu mengumpulkan ctid catatan dalam konteks isi lengkap baris tabel. Opsi paling sederhana adalah memasukkan seluruh 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)"}

Apakah mungkin untuk tidak melakukan casting?Pada prinsipnya, hal ini mungkin terjadi dalam banyak kasus. Sampai Anda mulai menggunakan kolom dalam tabel ini tipe tanpa operator kesetaraan:

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, kita langsung melihat bahwa jika ada lebih dari satu entri dalam array, ini semua adalah klon. Mari kita tinggalkan saja:

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 yang suka menulis lebih pendekAnda juga dapat menulisnya seperti ini:

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

Karena nilai string serial itu sendiri tidak menarik bagi kami, kami membuangnya begitu saja dari kolom subkueri yang dikembalikan.

Tinggal sedikit lagi yang harus dilakukan - buat 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 kita periksa diri kita sendiri:

[lihat penjelasan.tensor.ru]

Ya, semuanya benar: 3 catatan kami dipilih untuk satu-satunya Seq Scan dari seluruh tabel, dan node Hapus digunakan untuk mencari data pass 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 menghapus banyak catatan, jangan lupa jalankan VACUUM ANALYZE.

Mari kita periksa tabel yang lebih besar dan jumlah duplikat yang lebih banyak:

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 penjelasan.tensor.ru]

Jadi, metode ini berhasil, tetapi harus digunakan dengan hati-hati. Karena untuk setiap record yang dihapus, ada satu halaman data yang dibaca di Tid Scan, dan satu lagi di Delete.

Sumber: www.habr.com