Malplenigo de klonaj registroj de tabelo sen PK

Estas situacioj kiam al tablo sen ĉefa ŝlosilo aŭ iu alia unika indekso, pro malatento, kompletaj klonoj de jam ekzistantaj rekordoj estas inkluzivitaj.

Ekzemple, la valoroj de kronologia metriko estas skribitaj en PostgreSQL per COPY-fluo, kaj tiam okazas subita fiasko, kaj parto de la tute identaj datumoj denove alvenas.

Kiel forigi la datumbazon de nenecesaj klonoj?

Kiam PK ne estas helpanto

La plej facila maniero estas unue malhelpi tian situacion okazi. Ekzemple, rulu PRIMARY KEY. Sed ĉi tio ne ĉiam eblas sen pliigi la volumon de stokitaj datumoj.

Ekzemple, se la precizeco de la fontsistemo estas pli alta ol la precizeco de la kampo en la datumbazo:

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}

Ĉu vi rimarkis? La retronombrado anstataŭ 00:00:02 estis registrita en la datumbazo kun ts sekundo pli frue, sed restis sufiĉe valida de aplika vidpunkto (finfine, la datumvaloroj estas malsamaj!).

Kompreneble vi povas fari ĝin PK(metriko, ts) - sed tiam ni ricevos enmetajn konfliktojn por validaj datumoj.

Povas fari PK(metriko, ts, datumoj) — sed tio multe pliigos ĝian volumon, kiun ni ne uzos.

Tial, la plej ĝusta opcio estas fari regulan ne-unikan indekson (metriko, ts) kaj trakti problemojn post la fakto, se ili ja ekestas.

"La klonika milito komenciĝis"

Okazis ia akcidento, kaj nun ni devas detrui la klonorekordojn de la tablo.

Ni modeligu la originajn datumojn:

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

Ĉi tie nia mano tremis trifoje, Ctrl+V blokiĝis, kaj nun...

Unue, ni komprenu, ke nia tablo povas esti tre granda, do post kiam ni trovas ĉiujn klonojn, estas konsilinde, ke ni laŭvorte "piki nian fingron" por forigi specifajn rekordojn sen re-serĉi ilin.

Kaj ekzistas tia maniero - ĉi tio alparolado per ctid, la fizika identigilo de specifa rekordo.

Tio estas, antaŭ ĉio, ni devas kolekti la ctid de registroj en la kunteksto de la kompleta enhavo de la tabelvico. La plej simpla opcio estas ĵeti la tutan linion en tekston:

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

Ĉu eblas ne gisi?Principe, ĝi eblas en la plej multaj kazoj. Ĝis vi komencos uzi kampojn en ĉi tiu tabelo tipoj sen egaleca operatoro:

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

Jes, ni tuj vidas, ke se estas pli ol unu eniro en la tabelo, ĉi tiuj estas ĉiuj klonoj. Ni nur lasu ilin:

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)

Por tiuj, kiuj ŝatas skribi pli mallonganVi ankaŭ povas skribi ĝin tiel:

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

Ĉar la valoro de la seriigita ĉeno mem ne estas interesa por ni, ni simple forĵetis ĝin el la revenitaj kolumnoj de la subdemandado.

Restas nur iom por fari - igu DELETE uzi la aron, kiun ni ricevis:

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

Ni kontrolu nin:

[vidi ĉe explic.tensor.ru]

Jes, ĉio estas ĝusta: niaj 3 rekordoj estis elektitaj por la nura Seq Scan de la tuta tabelo, kaj la Forigi-nodo estis uzata por serĉi datumojn. ununura enirpermesilo kun Tid Scan:

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

Se vi malbaris multajn rekordojn, ne forgesu ruli VACUUM ANALYZE.

Ni kontrolu por pli granda tabelo kaj kun pli granda nombro da duplikatoj:

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;

[vidi ĉe explic.tensor.ru]

Do, la metodo funkcias sukcese, sed ĝi devas esti uzata kun ioma singardo. Ĉar por ĉiu rekordo, kiu estas forigita, estas unu datumpaĝo legita en Tid Scan, kaj unu en Forigi.

fonto: www.habr.com