Netwaye dosye klonaj nan yon tab san PK

Gen sitiyasyon lè nan yon tab san yon kle prensipal oswa kèk lòt endèks inik, akòz yon sipèvizyon, klon konplè dosye ki deja egziste yo enkli.

Pou egzanp, valè yon metrik kwonolojik yo ekri nan PostgreSQL lè l sèvi avèk yon kouran COPY, ak Lè sa a, gen yon echèk toudenkou, ak yon pati nan done yo konplètman idantik rive ankò.

Ki jan yo debarase baz done a nan klon nesesè?

Lè PK pa yon èd

Fason ki pi fasil la se anpeche yon sitiyasyon konsa rive an plas an premye. Pa egzanp, woule KLE PRIMÈ. Men, sa a se pa toujou posib san yo pa ogmante volim nan done ki estoke.

Pou egzanp, si presizyon nan sistèm sous la pi wo pase presizyon nan jaden an nan baz done a:

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}

Ou remake? Dekont la olye pou yo 00:00:02 te anrejistre nan baz done a ak ts yon dezyèm pi bonè, men li te rete byen valab nan yon pwen de vi aplikasyon (apre tout, valè done yo diferan!).

Natirèlman ou ka fè li PK(metrik, ts) - men Lè sa a, nou pral jwenn konfli ensèsyon pou done ki valab.

Kapab fè PK (metrik, ts, done) - men sa a pral anpil ogmante volim li yo, ki nou pa pral sèvi ak.

Se poutèt sa, opsyon ki pi kòrèk la se fè yon endèks regilye ki pa inik (metrik, ts) epi fè fas ak pwoblèm apre reyalite a si yo rive.

"Lagè klonik la kòmanse"

Gen kèk kalite aksidan ki te pase, epi kounye a nou gen detwi dosye yo klonaj ki soti nan tab la.

Ann modle done orijinal yo:

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

Isit la men nou tranble twa fwa, Ctrl + V te bloke, e kounye a...

Premyèman, se pou nou konprann ke tab nou an ka gwo anpil, kidonk apre nou fin jwenn tout klon yo, li rekòmande pou nou literalman "pike dwèt nou" pou efase. dosye espesifik san yo pa re-recherche yo.

Epi gen yon fason sa a - sa a adrese pa ctid, idantifyan fizik yon dosye espesifik.

Sa vle di, anvan tout bagay, nou bezwen kolekte ctid dosye yo nan yon kontèks kontni konplè ranje tab la. Opsyon ki pi senp la se jete tout liy lan nan tèks:

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

Èske li posib pa jete?Nan prensip, li posib nan pifò ka yo. Jiskaske ou kòmanse sèvi ak jaden nan tablo sa a kalite san operatè egalite:

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

Wi, nou imedyatman wè ke si gen plis pase yon antre nan etalaj la, sa yo se tout klon. Ann jis kite yo:

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)

Pou moun ki renmen ekri pi koutOu ka ekri li tou tankou sa a:

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

Depi valè fisèl seri a tèt li pa enteresan pou nou, nou tou senpleman jete l 'soti nan kolòn yo retounen nan subquery la.

Genyen jis yon ti kras ki rete pou fè - fè DELETE sèvi ak seri nou te resevwa a:

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

Ann tcheke tèt nou:

[wè nan eksplike.tensor.ru]

Wi, tout bagay kòrèk: 3 dosye nou yo te chwazi pou sèlman Seq Scan la nan tout tab la, epi yo te itilize node Efase pou chèche done. yon sèl pas ak Tid Scan:

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

Si ou efase anpil dosye, pa bliye kouri VACUUM ANALYZE.

Ann tcheke pou yon tab ki pi gwo ak yon pi gwo kantite kopi:

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;

[wè nan eksplike.tensor.ru]

Se konsa, metòd la travay avèk siksè, men li dwe itilize ak kèk prekosyon. Paske pou chak dosye ki efase, gen yon paj done li nan Tid Scan, ak youn nan Efase.

Sous: www.habr.com