Rensa klonposter från en tabell utan PK

Det finns situationer när till en tabell utan en primärnyckel eller något annat unikt index, på grund av en förbiseende, ingår kompletta kloner av redan existerande poster.

Till exempel skrivs värdena för ett kronologiskt mått in i PostgreSQL med en COPY-ström, och sedan uppstår ett plötsligt misslyckande, och en del av den helt identiska datan kommer igen.

Hur befrias databasen från onödiga kloner?

När PK inte är en hjälpare

Det enklaste sättet är att förhindra att en sådan situation uppstår i första hand. Till exempel, rulla PRIMARY KEY. Men detta är inte alltid möjligt utan att öka volymen lagrad data.

Till exempel, om noggrannheten i källsystemet är högre än noggrannheten för fältet i databasen:

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}

Märkte du? Nedräkningen istället för 00:00:02 registrerades i databasen med ts en sekund tidigare, men förblev ganska giltig ur tillämpningssynpunkt (trots allt är datavärdena olika!).

Självklart kan du göra det PK(metrisk, ts) - men då kommer vi att få insättningskonflikter för giltig data.

Kan göra PK(mått, ts, data) - men detta kommer att öka volymen avsevärt, vilket vi inte kommer att använda.

Därför är det mest korrekta alternativet att göra ett vanligt icke-unikt index (metrisk, ts) och ta itu med problem i efterhand om de uppstår.

"Det kloniska kriget har börjat"

Någon sorts olycka hände, och nu måste vi förstöra klonposterna från bordet.

Låt oss modellera originaldata:

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

Här darrade vår hand tre gånger, Ctrl+V fastnade, och nu...

Först, låt oss förstå att vårt bord kan vara mycket stort, så efter att vi hittat alla kloner, är det tillrådligt för oss att bokstavligen "peta oss i fingret" för att radera specifika poster utan att söka efter dem.

Och det finns ett sådant sätt - det här adressering av ctid, den fysiska identifieraren för en specifik post.

Det vill säga, först och främst måste vi samla in ctid av poster i samband med det fullständiga innehållet i tabellraden. Det enklaste alternativet är att kasta hela raden till text:

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

Är det möjligt att inte kasta?I princip är det möjligt i de flesta fall. Tills du börjar använda fält i den här tabellen typer utan jämställdhetsoperatör:

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, vi ser omedelbart att om det finns mer än en post i arrayen är dessa alla kloner. Låt oss bara lämna dem:

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)

För dig som gillar att skriva kortareDu kan också skriva så här:

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

Eftersom värdet på den serialiserade strängen i sig inte är intressant för oss, kastade vi helt enkelt ut det från de returnerade kolumnerna i underfrågan.

Det är bara lite kvar att göra - få DELETE att använda uppsättningen vi fick:

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

Låt oss kontrollera oss själva:

[titta på explain.tensor.ru]

Ja, allt är korrekt: våra 3 poster valdes ut för den enda Seq Scan av hela tabellen, och Delete-noden användes för att söka efter data enkelpass med Tid Scan:

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

Om du rensade många poster, glöm inte att köra VACUUM ANALYZE.

Låt oss leta efter ett större bord och med ett större antal dubbletter:

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;

[titta på explain.tensor.ru]

Så metoden fungerar framgångsrikt, men den måste användas med viss försiktighet. För för varje post som raderas finns det en datasida som läses i Tid Scan, och en i Delete.

Källa: will.com