Klónrekordok törlése PK nélküli táblából

Vannak helyzetek, amikor elsődleges kulcs nélküli táblához vagy más egyedi index, egy felügyelet miatt a már meglévő rekordok teljes klónjai szerepelnek.

Például egy kronológiai metrika értékeit egy COPY folyam segítségével írják be a PostgreSQL-be, majd hirtelen hiba történik, és a teljesen azonos adatok egy része újra megérkezik.

Hogyan lehet megszabadítani az adatbázist a felesleges klónoktól?

Amikor PK nem segítő

A legegyszerűbb módja annak, hogy eleve megelőzzük az ilyen helyzet előfordulását. Például dobja az ELSŐDLEGES KULCS billentyűt. De ez nem mindig lehetséges a tárolt adatok mennyiségének növelése nélkül.

Például, ha a forrásrendszer pontossága nagyobb, mint az adatbázisban lévő mező pontossága:

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}

Észrevetted? A visszaszámlálás 00:00:02 helyett ts-sel egy másodperccel korábban került rögzítésre az adatbázisban, de alkalmazási szempontból meglehetősen érvényes maradt (elvégre az adatértékek eltérőek!).

Természetesen megteheti PK(metrika, ts) - de ekkor beillesztési ütközéseket kapunk az érvényes adatoknál.

Megteheti PK(metrika, ts, adat) - de ez nagymértékben növeli a hangerőt, amit nem fogunk használni.

Ezért a leghelyesebb megoldás egy szabályos, nem egyedi index készítése (metrika, ts) és utólag kezelni a problémákat, ha mégis felmerülnek.

"A klón háború elkezdődött"

Valamiféle baleset történt, és most meg kell semmisítenünk a klón rekordokat az asztalról.

Modellezzük az eredeti adatokat:

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

Itt háromszor remegett a kezünk, elakadt a Ctrl+V, és most...

Először is értsük meg, hogy a táblázatunk nagyon nagy lehet, így miután megtaláltuk az összes klónt, tanácsos szó szerint „böknünk az ujjunkat” a törléshez. konkrét rekordokat anélkül, hogy újra megkeresné azokat.

És van egy ilyen módszer - ez ctid általi címzés, egy adott rekord fizikai azonosítója.

Azaz mindenekelőtt össze kell gyűjtenünk a rekordok ctidjét a táblázatsor teljes tartalmával összefüggésben. A legegyszerűbb lehetőség az egész sort szövegbe önteni:

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

Lehetséges nem leadni?Elvileg a legtöbb esetben lehetséges. Amíg el nem kezdi használni a mezőket ebben a táblázatban típusok egyenlőség operátor nélkül:

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

Igen, azonnal látjuk, hogy ha egynél több bejegyzés van a tömbben, ezek mind klónok. Hagyjuk őket:

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)

Azoknak, akik szeretnek rövidebben írniLeírhatod így is:

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

Mivel maga a szerializált karakterlánc értéke számunkra nem érdekes, egyszerűen kidobtuk az allekérdezés visszaadott oszlopai közül.

Már csak egy kis tennivaló van hátra – használd a DELETE-t a kapott készlettel:

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

Vizsgáljuk meg magunkat:

[megtekintés itt: magyarázat.tensor.ru]

Igen, minden rendben van: a 3 rekordunkat a teljes tábla egyetlen Seq Scan számára választottuk ki, és a Delete csomópontot használtuk az adatok keresésére. egyszeri belépő a Tid Scan segítségével:

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

Ha sok rekordot törölt, ne felejtse el futtatni a VÁKUUMANALÍZIT.

Nézzünk meg egy nagyobb táblát és nagyobb számú ismétlődést:

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;

[megtekintés itt: magyarázat.tensor.ru]

Tehát a módszer sikeresen működik, de óvatosan kell használni. Mert minden törölt rekordhoz egy adatoldal kerül beolvasásra a Tid Scanben és egy a Törlésben.

Forrás: will.com