ניקוי רשומות שיבוט מטבלה ללא PK

יש מצבים שבהם לטבלה ללא מפתח ראשי או אינדקס ייחודי אחר, עקב פיקוח, כלולים שיבוטים שלמים של רשומות שכבר קיימות.

לדוגמה, הערכים של מדד כרונולוגי נכתבים לתוך PostgreSQL באמצעות זרם COPY, ואז יש כשל פתאומי, וחלק מהנתונים הזהים לחלוטין מגיע שוב.

איך נפטרים מהמאגר משבטים מיותרים?

כשפ"ק אינו עוזר

הדרך הקלה ביותר היא למנוע מצב כזה מלכתחילה. לדוגמה, גלגל את PRIMARY KEY. אבל זה לא תמיד אפשרי בלי להגדיל את נפח הנתונים המאוחסנים.

לדוגמה, אם הדיוק של מערכת המקור גבוה מהדיוק של השדה במסד הנתונים:

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(metric, ts, data) - אבל זה יגדיל מאוד את הנפח שלו, שלא נשתמש בו.

לכן, האפשרות הנכונה ביותר היא לעשות אינדקס רגיל שאינו ייחודי (מדד, ts) ולהתמודד עם בעיות לאחר מעשה אם הן אכן מתעוררות.

"המלחמה הקלונית החלה"

קרתה איזושהי תאונה, ועכשיו אנחנו צריכים להשמיד את רישומי השיבוט מהשולחן.

בואו נעצב את הנתונים המקוריים:

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

בואו נבדוק את עצמנו:

[הסתכל ב-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;

[הסתכל ב-explain.tensor.ru]

אז, השיטה עובדת בהצלחה, אבל יש להשתמש בה בזהירות מסוימת. כי על כל רשומה שנמחקת, יש דף נתונים אחד שנקרא ב-Tid Scan, ואחד ב-Delete.

מקור: www.habr.com