PK शिवाय टेबलमधून क्लोन रेकॉर्ड साफ करणे

अशी परिस्थिती असते जेव्हा प्राथमिक किल्लीशिवाय टेबलवर किंवा इतर काही अनन्य निर्देशांक, एका निरीक्षणामुळे, आधीच अस्तित्वात असलेल्या रेकॉर्डचे पूर्ण क्लोन समाविष्ट केले आहेत.

उदाहरणार्थ, कालक्रमानुसार मेट्रिकची मूल्ये कॉपी प्रवाह वापरून 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 ऐवजी काउंटडाउन एका सेकंदापूर्वी 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[]);

चला स्वतः तपासूया:

[explain.tensor.ru पहा]

होय, सर्व काही बरोबर आहे: आमच्या 3 रेकॉर्ड संपूर्ण टेबलच्या फक्त Seq स्कॅनसाठी निवडल्या गेल्या आणि डेटा शोधण्यासाठी हटवा नोड वापरला गेला. टिड स्कॅनसह एकल पास:

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

[explain.tensor.ru पहा]

म्हणून, पद्धत यशस्वीरित्या कार्य करते, परंतु ती काही सावधगिरीने वापरली पाहिजे. कारण हटवलेल्या प्रत्येक रेकॉर्डसाठी, टिड स्कॅनमध्ये वाचलेले एक डेटा पृष्ठ आहे आणि एक हटवा.

स्त्रोत: www.habr.com