पीके के बिना किसी तालिका से क्लोन रिकॉर्ड साफ़ करना

ऐसी स्थितियाँ होती हैं जब प्राथमिक कुंजी के बिना किसी तालिका में या किसी अन्य अद्वितीय सूचकांक में, किसी चूक के कारण, पहले से मौजूद रिकॉर्ड के पूर्ण क्लोन शामिल कर दिए जाते हैं।

उदाहरण के लिए, कालानुक्रमिक मीट्रिक के मान COPY स्ट्रीम का उपयोग करके PostgreSQL में लिखे जाते हैं, और फिर अचानक विफलता होती है, और पूरी तरह से समान डेटा का हिस्सा फिर से आता है।

अनावश्यक क्लोनों के डेटाबेस से कैसे छुटकारा पाएं?

जब पीके मददगार नहीं है

सबसे आसान तरीका यह है कि ऐसी स्थिति को पहली बार में ही उत्पन्न होने से रोका जाए। उदाहरण के लिए, प्राथमिक कुंजी रोल करें. लेकिन संग्रहीत डेटा की मात्रा बढ़ाए बिना यह हमेशा संभव नहीं है।

उदाहरण के लिए, यदि स्रोत प्रणाली की सटीकता डेटाबेस में फ़ील्ड की सटीकता से अधिक है:

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 के बजाय उलटी गिनती डेटाबेस में टीएस के साथ एक सेकंड पहले दर्ज की गई थी, लेकिन एप्लिकेशन के दृष्टिकोण से काफी मान्य रही (आखिरकार, डेटा मान अलग हैं!)।

बेशक आप यह कर सकते हैं पीके(मीट्रिक, टीएस) - लेकिन फिर हमें वैध डेटा के लिए सम्मिलन विरोध मिलेगा।

कर सकते हैं पीके (मीट्रिक, टीएस, डेटा) - लेकिन इससे इसका वॉल्यूम काफी बढ़ जाएगा, जिसका हम इस्तेमाल नहीं करेंगे।

इसलिए, सबसे सही विकल्प एक नियमित गैर-विशिष्ट सूचकांक बनाना है (मीट्रिक, टीएस) और यदि समस्याएँ उत्पन्न होती हैं तो उनसे निपटें।

"क्लोनिक युद्ध शुरू हो गया है"

किसी प्रकार की दुर्घटना हुई, और अब हमें तालिका से क्लोन रिकॉर्ड को नष्ट करना होगा।

आइए मूल डेटा का मॉडल बनाएं:

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 अटक गया और अब...

सबसे पहले, आइए समझें कि हमारी तालिका बहुत बड़ी हो सकती है, इसलिए सभी क्लोन ढूंढने के बाद, हमें हटाने के लिए शाब्दिक रूप से "अपनी उंगली को पोछना" उचित है विशिष्ट अभिलेखों को दोबारा खोजे बिना.

और एक ऐसा तरीका है - यह सीटीआईडी ​​द्वारा संबोधित करना, एक विशिष्ट रिकॉर्ड का भौतिक पहचानकर्ता।

अर्थात्, सबसे पहले, हमें तालिका पंक्ति की संपूर्ण सामग्री के संदर्भ में अभिलेखों की सीटीआईडी ​​एकत्र करने की आवश्यकता है। सबसे आसान विकल्प पूरी पंक्ति को टेक्स्ट में डालना है:

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

आइए स्वयं जाँचें:

[देखें व्याख्या.टेंसर.आरयू]

हां, सब कुछ सही है: हमारे 3 रिकॉर्ड संपूर्ण तालिका के एकमात्र Seq स्कैन के लिए चुने गए थे, और डेटा की खोज के लिए डिलीट नोड का उपयोग किया गया था टिड स्कैन के साथ सिंगल पास:

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

[देखें व्याख्या.टेंसर.आरयू]

तो, विधि सफलतापूर्वक काम करती है, लेकिन इसका उपयोग कुछ सावधानी के साथ किया जाना चाहिए। क्योंकि हटाए गए प्रत्येक रिकॉर्ड के लिए, टिड स्कैन में एक डेटा पेज पढ़ा जाता है, और डिलीट में एक।

स्रोत: www.habr.com