PK बिना तालिकाबाट क्लोन रेकर्डहरू खाली गर्दै

त्यहाँ परिस्थितिहरू छन् जब प्राथमिक कुञ्जी बिनाको टेबलमा वा केही अन्य अद्वितीय अनुक्रमणिका, एक निरीक्षणको कारण, पहिले नै अवस्थित रेकर्डहरूको पूर्ण क्लोनहरू समावेश छन्।

उदाहरण को लागी, एक क्रोनोलॉजिकल मेट्रिक को मानहरू पोस्टग्रेएसक्यूएल मा प्रतिलिपि स्ट्रिम प्रयोग गरेर लेखिएको छ, र त्यसपछि त्यहाँ अचानक विफलता छ, र पूर्ण रूपमा समान डेटाको अंश फेरि आउँछ।

अनावश्यक क्लोनहरूको डाटाबेस कसरी हटाउने?

जब 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, डेटा) - तर यसले यसको मात्रा धेरै बढाउँछ, जुन हामी प्रयोग गर्दैनौं।

त्यसकारण, सबैभन्दा सही विकल्प नियमित गैर-अद्वितीय सूचकांक बनाउनु हो (मेट्रिक, टीएस) र यदि तिनीहरू उत्पन्न हुन्छन् भने तथ्य पछि समस्याहरू समाधान गर्नुहोस्।

"क्लोनिक युद्ध सुरु भएको छ"

केहि प्रकारको दुर्घटना भयो, र अब हामीले टेबलबाट क्लोन रेकर्डहरू नष्ट गर्नु पर्छ।

मूल डाटा मोडेल गरौं:

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

आफैलाई जाँच गरौं:

[explan.tensor.ru मा हेर्नुहोस्]

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

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

[explan.tensor.ru मा हेर्नुहोस्]

त्यसैले, विधि सफलतापूर्वक काम गर्दछ, तर यो केहि सावधानी संग प्रयोग गर्नुपर्छ। किनभने मेटाइएका प्रत्येक रेकर्डको लागि, टिड स्क्यानमा पढ्ने एउटा डाटा पृष्ठ हुन्छ, र एउटा मेटाइन्छ।

स्रोत: www.habr.com