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

هناك حالات عندما ، في جدول بدون مفتاح أساسي أو فهرس فريد آخر ، تقع النسخ الكاملة للسجلات الموجودة بالفعل عن غير قصد.



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

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

عندما PK ليس مساعد

أسهل طريقة هي منع حدوث ذلك على الإطلاق. على سبيل المثال ، قم بلف نفس مفتاح PRIMARY. لكن هذا غير ممكن دائمًا دون زيادة كمية البيانات المخزنة.

على سبيل المثال ، إذا كانت دقة النظام الأصلي أعلى من دقة الحقل في قاعدة البيانات:

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

يمكنك إنشاء PK (metric ، ts ، بيانات) - ولكن هذا سيزيد من حجمه بشكل كبير ، والذي لن نستخدمه.

لذلك ، فإن الخيار الأصح هو إنشاء فهرس غير فريد (متري ، ts) والتعامل مع المشكلات بعد حدوثها ، إذا نشأت.

"لقد بدأت الحرب clonic"

حدث نوع من الحوادث ، والآن يتعين علينا تدمير سجلات النسخ من الجدول.



دعونا محاكاة البيانات المصدر:

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

نعم ، سنرى على الفور أنه إذا كان هناك أكثر من سجل واحد في المجموعة ، فهناك كل ما هو موجود. دعنا نتركهم فقط:

 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) 

نظرًا لأن قيمة السلسلة التسلسلية نفسها ليست مثيرة للاهتمام بالنسبة إلينا ، فقد أخرجناها ببساطة من الأعمدة التي تم إرجاعها من الاستعلام الفرعي.

كل ما تبقى هو الحصول على 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 وواحدة في الحذف.