डीबीए: पीके के बिना एक मेज से क्लोन रिकॉर्ड को साफ़ करना

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



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

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

जब पीके एक सहायक नहीं है

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

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

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 के साथ डेटाबेस को लिखी गई थी, लेकिन आवेदन के दृष्टिकोण से काफी वैध बनी रही (आखिरकार, डेटा मान भिन्न हैं!)।

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

आप पीके (मीट्रिक, टीएस, डेटा) बना सकते हैं - लेकिन इससे इसकी मात्रा बहुत बढ़ जाएगी, जिसका हम उपयोग नहीं करेंगे।

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

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

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



स्रोत डेटा का अनुकरण करते हैं:

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

अपने आप को जांचें:


[स्पष्टीकरण पर देखें ।ensor.ru]

हां, सब कुछ सही है: हमारे 3 रिकॉर्ड पूरे तालिका के एकमात्र Seq स्कैन के लिए चुने गए थे, और हटाएँ नोड ने 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; 

[स्पष्टीकरण पर देखें ।ensor.ru]

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