Kloonrecords uit een tabel wissen zonder PK

Er zijn situaties waarin naar een tabel zonder primaire sleutel of een andere unieke index, als gevolg van een vergissing zijn volledige klonen van reeds bestaande records opgenomen.

De waarden van een chronologische metriek worden bijvoorbeeld in PostgreSQL geschreven met behulp van een COPY-stream, en dan is er een plotselinge storing en komt een deel van de volledig identieke gegevens opnieuw binnen.

Hoe kan ik de database ontdoen van onnodige klonen?

Wanneer PK geen helper is

De eenvoudigste manier is om te voorkomen dat een dergelijke situatie zich überhaupt voordoet. Rol bijvoorbeeld PRIMARY KEY. Maar dit is niet altijd mogelijk zonder de hoeveelheid opgeslagen gegevens te vergroten.

Als de nauwkeurigheid van het bronsysteem bijvoorbeeld hoger is dan de nauwkeurigheid van het veld in de database:

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}

Heb je het gemerkt? Het aftellen in plaats van 00:00:02 werd een seconde eerder met ts in de database vastgelegd, maar bleef vanuit toepassingsoogpunt behoorlijk geldig (de datawaarden zijn immers anders!).

Natuurlijk kun je het doen PK(metrisch, ts) - maar dan krijgen we invoegconflicten voor geldige gegevens.

Kan doen PK(metrisch, ts, gegevens) - maar dit zal het volume ervan enorm vergroten, wat we niet zullen gebruiken.

Daarom is de meest correcte optie om een ​​reguliere, niet-unieke index te maken (metrisch, ts) en problemen achteraf aanpakken als ze zich voordoen.

‘De klonische oorlog is begonnen’

Er is een ongeluk gebeurd en nu moeten we de kloonrecords van de tafel vernietigen.

Laten we de originele gegevens modelleren:

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

Hier trilde onze hand drie keer, Ctrl+V bleef steken, en nu...

Laten we eerst begrijpen dat onze tabel erg groot kan zijn, dus nadat we alle klonen hebben gevonden, is het raadzaam dat we letterlijk "met onze vinger prikken" om te verwijderen specifieke records zonder ze opnieuw te doorzoeken.

En er is zo'n manier - dit adressering door ctid, de fysieke identificatie van een specifiek record.

Dat wil zeggen dat we allereerst de ctid van records moeten verzamelen in de context van de volledige inhoud van de tabelrij. De eenvoudigste optie is om de hele regel in tekst om te zetten:

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)"}

Is het mogelijk om niet te casten?In principe is dit in de meeste gevallen mogelijk. Totdat u velden in deze tabel gaat gebruiken typen zonder gelijkheidsoperator:

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

Ja, we zien meteen dat als er meer dan één item in de array staat, dit allemaal klonen zijn. Laten we ze gewoon laten staan:

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)

Voor wie graag korter schrijftJe kunt het ook zo schrijven:

SELECT
  unnest((array_agg(ctid))[2:])
FROM
  tbl T
GROUP BY
  T::text;

Omdat de waarde van de geserialiseerde string zelf niet interessant voor ons is, hebben we deze eenvoudigweg uit de geretourneerde kolommen van de subquery verwijderd.

Er is nog een klein beetje te doen - laat DELETE de set gebruiken die we hebben ontvangen:

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

Laten we onszelf controleren:

[kijk naar explain.tensor.ru]

Ja, alles klopt: onze 3 records zijn geselecteerd voor de enige Seq Scan van de hele tabel en het knooppunt Verwijderen is gebruikt om naar gegevens te zoeken enkele pas met Tid Scan:

->  Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
      TID Cond: (ctid = ANY ($0))

Als u veel records hebt gewist, vergeet niet VACUÜMANALYSE uit te voeren.

Laten we kijken of er een grotere tabel is en met een groter aantal duplicaten:

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;

[kijk naar explain.tensor.ru]

De methode werkt dus met succes, maar moet met enige voorzichtigheid worden gebruikt. Omdat er voor elke record die wordt verwijderd, één gegevenspagina wordt gelezen in Tid Scan en één in Verwijderen.

Bron: www.habr.com