مسح سجلات الاستنساخ من جدول بدون PK

هناك حالات عندما إلى جدول بدون مفتاح أساسي أو بعض الفهارس الفريدة الأخرى، بسبب الإشراف، يتم تضمين النسخ الكاملة للسجلات الموجودة بالفعل.

على سبيل المثال، تتم كتابة قيم المقياس الزمني في PostgreSQL باستخدام دفق COPY، ثم يحدث فشل مفاجئ، ويصل جزء من البيانات المتطابقة تمامًا مرة أخرى.

كيفية تخليص قاعدة البيانات من النسخ غير الضرورية؟

عندما 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 (متري، نهاية الخبر) - ولكن بعد ذلك سنحصل على تعارضات في الإدراج للبيانات الصالحة.

يمكن القيام به PK (متري، 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، المعرف الفعلي لسجل معين.

أي أننا نحتاج أولاً وقبل كل شيء إلى جمع عدد السجلات في سياق المحتوى الكامل لصف الجدول. الخيار الأبسط هو تحويل السطر بأكمله إلى نص:

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

دعونا نتحقق من أنفسنا:

[انظر إلىشرح.tensor.ru]

نعم، كل شيء صحيح: تم اختيار سجلاتنا الثلاثة لإجراء المسح التسلسلي الوحيد للجدول بأكمله، وتم استخدام عقدة الحذف للبحث عن البيانات تمريرة واحدة مع Tid Scan:

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

إذا قمت بمسح الكثير من السجلات، لا تنس تشغيل تحليل الفراغ.

دعونا نتحقق من وجود جدول أكبر وبه عدد أكبر من التكرارات:

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;

[انظر إلىشرح.tensor.ru]

لذا فإن الطريقة تعمل بنجاح، ولكن يجب استخدامها مع بعض الحذر. لأنه لكل سجل يتم حذفه، هناك صفحة بيانات واحدة يتم قراءتها في Tid Scan، وواحدة في الحذف.

المصدر: www.habr.com