主持人 > 博客 > 管理 > 从没有 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 也会读取一个数据页。

来源： habr.com

添加评论