主持人 > 博客 > 管理 > 從沒有 PK 的表中清除克隆記錄

從沒有 PK 的表中清除克隆記錄

有一些情況，當 到沒有主鍵的表 或某些其他唯一索引，由於疏忽，已包含現有記錄的完整克隆。

從沒有 PK 的表中清除克隆記錄

例如，使用 COPY 流將時間順序度量的值寫入 PostgreSQL，然後突然發生故障，部分完全相同的資料再次到達。

如何清除資料庫中不必要的克隆？

當PK不是幫手時

最簡單的方法就是從一開始就防止這種情況發生。 例如，滾動主鍵。 但如果不增加儲存資料量，這並不總是可能的。

例如，如果來源系統的準確率高於資料庫中欄位的準確率：

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}

你注意到了嗎？ 倒數計時而不是 00:00:02 早一秒就用 ts 記錄在資料庫中，但從應用程式的角度來看仍然相當有效（畢竟資料值不同！）。

你當然可以做到 PK（公制，ts） - 但隨後我們會遇到有效資料的插入衝突。

可以做 PK（公制、ts、數據） - 但這會大大增加它的體積，我們不會使用它。

因此，最正確的選擇是製作常規非唯一索引 （公制，ts） 並在出現問題後進行處理。

“複製戰爭已經開始”

發生了某種意外，現在我們必須從表中銷毀克隆記錄。

從沒有 PK 的表中清除克隆記錄

讓我們對原始資料進行建模：

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

這裡我們的手顫抖了三下，Ctrl+V被卡住了，現在...

首先，我們要明白，我們的表可能非常大，所以在找到所有克隆後，建議我們直接「戳一下手指」來刪除 具體記錄無需研究.

還有這樣一個方法──這個 按 CTID 尋址，特定記錄的實體識別碼。

也就是說，首先我們需要收集表格行完整內容上下文中所記錄的ctid。 最簡單的選擇是將整行轉換為文字：

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

可以不投嗎？原則上，大多數情況下是可以的。 直到您開始使用此表中的字段 不帶相等運算子的類型:

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

是的，我們立即看到，如果數組中有多個條目，那麼這些都是克隆。 讓我們離開他們：

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)

對於那些喜歡寫短的人你也可以這樣寫：

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

由於我們對序列化字串本身的值不感興趣，因此我們只是將其從子查詢的傳回列中丟棄。

還剩下一點點要做 - 讓 DELETE 使用我們收到的集合：

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

我們自己檢查一下：

從沒有 PK 的表中清除克隆記錄
[看看 explain.tensor.ru]

是的，一切都正確：我們選擇了3筆記錄進行全表唯一的Seq Scan，並使用Delete節點來搜尋數據 使用 Tid Scan 進行單次掃描:

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

如果你清除了很多記錄， 不要忘記運行 VACUUM ANALYZE.

讓我們檢查一下是否有更大的表和更多的重複項：

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;

從沒有 PK 的表中清除克隆記錄
[看看 explain.tensor.ru]

因此，該方法很有效，但必須謹慎使用。 因為每刪除一筆記錄，Tid Scan 都會讀取一個資料頁，Delete 也會讀取一個資料頁。

來源： www.habr.com

添加評論