Εκκαθάριση εγγραφών κλώνων από πίνακα χωρίς PK

Υπάρχουν καταστάσεις όταν σε έναν πίνακα χωρίς πρωτεύον κλειδί ή κάποιο άλλο μοναδικό ευρετήριο, λόγω παράβλεψης, περιλαμβάνονται πλήρεις κλώνοι ήδη υπαρχουσών εγγραφών.

Για παράδειγμα, οι τιμές μιας χρονολογικής μέτρησης εγγράφονται στο PostgreSQL χρησιμοποιώντας μια ροή COPY και, στη συνέχεια, υπάρχει μια ξαφνική αποτυχία και μέρος των εντελώς πανομοιότυπων δεδομένων φτάνει ξανά.

Πώς να απαλλάξετε τη βάση δεδομένων από περιττούς κλώνους;

Όταν ο ΠΚ δεν είναι βοηθός

Ο ευκολότερος τρόπος είναι να αποτρέψετε εξαρχής μια τέτοια κατάσταση. Για παράδειγμα, κυλήστε το PRIMARY KEY. Αλλά αυτό δεν είναι πάντα δυνατό χωρίς αύξηση του όγκου των αποθηκευμένων δεδομένων.

Για παράδειγμα, εάν η ακρίβεια του συστήματος πηγής είναι μεγαλύτερη από την ακρίβεια του πεδίου στη βάση δεδομένων:

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, δεδομένα) - αλλά αυτό θα αυξήσει πολύ τον όγκο του, τον οποίο δεν θα χρησιμοποιήσουμε.

Επομένως, η πιο σωστή επιλογή είναι να δημιουργήσετε ένα κανονικό μη μοναδικό ευρετήριο (μετρική, 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[]);

Ας ελέγξουμε τον εαυτό μας:

[κοιτάξτε στο εξηγήστε.tensor.ru]

Ναι, όλα είναι σωστά: οι 3 εγγραφές μας επιλέχθηκαν για τη μοναδική Seq Scan ολόκληρου του πίνακα και ο κόμβος Delete χρησιμοποιήθηκε για την αναζήτηση δεδομένων μονό πέρασμα με Tid Scan:

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

[κοιτάξτε στο εξηγήστε.tensor.ru]

Έτσι, η μέθοδος λειτουργεί με επιτυχία, αλλά πρέπει να χρησιμοποιείται με κάποια προσοχή. Επειδή για κάθε εγγραφή που διαγράφεται, υπάρχει μία σελίδα δεδομένων που διαβάζεται στο Tid Scan και μία στο Delete.

Πηγή: www.habr.com